Tecnologia da Informação

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.

- Postgresql - Calcular Subtotal / Total - Equivalente Ao With Rollup No Mysql
Neste artigo, vamos mostrar 2 exemplos de como utilizar a função "SUM" para calcular o subtotal / total em uma consulta. Para quem usa o Mysql, a consulta que vamos fazer é semelhante ao "WITH ROLLUP". Caso ainda não conheça a função...

- Mysql Sum - Soma
A função SUM retorna a soma de valores de uma coluna. SINTAXE SELECT SUM(nome_da_coluna) FROM nome_da_tabela; Para os 6 exemplos abaixo, utilizaremos a tabela "tb_fornecedor". Veja a imagem abaixo: Caso tenha interesse faça o download dos...

- Postgresql Avg - Média
A função AVG retorna a média de valores de uma coluna. Serão descritos 6 exemplos de utilização desta função. Caso tenha interesse faça o download dos exemplos ou veja os scripts no github. SINTAXESELECT AVG(nome_da_coluna)...

- Postgresql - Formatar Data
Serão descritos 2 exemplos de como formatar uma data. 1º Exemplo: exibir a data no formato dd/mm/aaaa (dia/mês/ano): Solução: vamos utilizar a função to_char para formatar a data. Na imagem abaixo, vemos a tabela "tb_fornecedor", vamos...

- Postgresql Sum - Soma
A função SUM retorna a soma de valores de uma coluna. SINTAXE SELECT SUM(nome_da_coluna) FROM nome_da_tabela; Para os 6 exemplos abaixo, utilizaremos a tabela "tb_fornecedor". Veja a imagem abaixo: 1º Exemplo Cenário: um funcionário quer...

Tecnologia da Informação