ORACLE - Calcular total /subtotal com ROLLUP

Neste artigo, vamos mostrar 3 exemplos de como utilizar a função "SUM" acompanhada do comando "ROLLUP" para calcular o subtotal / total em uma consulta.

Caso tenha interesse, faça o download ou veja os scripts deste artigo no github.
Você também pode executar os scripts e visualizar as tabelas no sqlfiddle. O script foi testado no Oracle 11g R2.

1º  Exemplo

Vamos calcular as despesas com fornecedor para cada segmento de uma empresa.
Iremos exibir o subtotal por segmento: "Papelaria e informática", "Marcenaria", "Serralheria" e "Limpeza e higiêne".
Também vamos exibir no final da consulta o total geral para todos os segmentos.
Neste exemplo, utilizaremos a tabela "tb_fornecedor". Veja a imagem da tabela a seguir:

Solução

Vamos utilizar a função "SUM" acompanhada de "GROUP BY" para fazer a quebra por segmento. Além disso vamos utilizar o "ROLLUP" para exibir o subtotal embaixo de cada quebra e o total geral no último registro.

Veja a sentença abaixo:

SELECT
    segmento,
    produto,
    SUM(valor)
FROM tb_fornecedor
GROUP BY ROLLUP(segmento, produto);

Após a execução da sentença teremos o resultado exibido a seguir:

2º  Exemplo

Para facilitarmos a interpretação do resultado da consulta vamos alterar o conteúdo da primeira coluna:

• Nos registros que exibirem o subtotal, a primeira coluna será acompanhada da palavra "Subtotal" e do nome do segmento;
• O ultimo registro da primeira coluna se chamará "Total Geral";
• Nos outros registros o nome do segmento será mantido;

Solução

Veja a sentença abaixo: 

SELECT
CASE
WHEN (segmento IS NULL AND produto IS NULL) 
  THEN 'Total Geral'
WHEN (segmento IS NOT NULL AND produto IS NULL) 
  THEN  'Subtotal' || ' - ' || segmento  
ELSE segmento
END AS segmento,
produto,
SUM(valor)
FROM tb_fornecedor GROUP BY ROLLUP (segmento, produto);

Após a execução da sentença teremos o resultado exibido a seguir:

Foi utilizado o comando "CASE WHEN" na primeira coluna para verificar:

• se o segmento e o produto eram nulos,  caso fossem, houve a substituição por "Total Geral";
• se o segmento não era nulo e o produto era nulo, caso fossem, houve a substituição por "Subtotal" concatenado com o nome do segmento;
• se nenhuma condição acima fosse verdadeira, mantivemos o conteúdo da coluna segmento;

Observação: utilizamos o operador de concatenação "||" para unir a palavra "Subtotal" ao conteúdo da coluna segmento;

Também podemos indicar o total geral na primeira coluna e os subtotais na segunda coluna.  Veja a sentença abaixo:

SELECT
CASE
WHEN (segmento IS NULL AND produto IS NULL) 
  THEN 'Total Geral'
ELSE segmento
END AS segmento,
CASE
WHEN (segmento IS NOT NULL AND produto IS NULL) 
  THEN 'Subtotal' || ' - ' || segmento
ELSE produto 
END AS produto,
SUM(valor) AS valor
FROM tb_fornecedor
GROUP BY ROLLUP (segmento, produto);

Após a execução da sentença teremos o resultado exibido a seguir:

3º  Exemplo

Neste exemplo, vamos mostrar como ordenar as colunas "segmento" e "produto", mantendo o "Total Geral" no último registro e o subtotal no final de cada segmento.

Solução

Vamos utilizar "ORDER BY" em uma subquery (subconsulta).

Veja a sentença abaixo: 

SELECT
COALESCE(segmento, 'TOTAL GERAL')  AS segmento,
CASE
 WHEN ((segmento IS NOT NULL) AND (produto IS NULL))
  THEN CONCAT('Subtotal - ', segmento)
  WHEN (segmento IS NOT NULL AND produto IS NOT NULL)
  THEN produto
END AS produto,
valor FROM
(
 SELECT
 segmento, 
 CASE
  WHEN(segmento IS NOT NULL) THEN 0
  ELSE 1
 END AS segmento_ordem, 
 produto, 
 CASE
  WHEN(produto IS NOT NULL) THEN 0
 ELSE 1
 END  AS produto_ordem,
 SUM(valor) AS valor
 FROM tb_fornecedor
 GROUP BY ROLLUP(segmento, produto)
) TOTAL
ORDER BY
segmento,
segmento_ordem,
produto_ordem,
produto;

A subquery que criamos está destacada na cor azul e está posicionada entre parenteses, esta subquery recebeu o alias (apelido) de TOTAL.

Observação: perceba que utilizamos duas vezes o "CASE WHEN" na subquery:

• o primeiro para que o "TOTAL GERAL" fosse exibido no último registo da coluna "segmento" independente da ordem alfabética;
• o segundo para que o "Subtotal" fosse exibido no final de cada segmento da coluna "produto" independente da ordem alfabética;

Após a execução da sentença, teremos o resultado exibido a seguir:

Deixe o seu comentário ou sugestão.
Gostou?  Siga no Google +  ou Facebook.