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 "SUM", veja o artigo "PostgreSql SUM - Soma "

Caso tenha interesse, faça o download ou veja o script deste artigo no github.
O script está disponível para execução através do SQL Fiddle e foi testado no PostgreSql 9.1.9.

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

Temos duas formas de fazer esta consulta, podemos utilizar subquery ou o comando WITH, vamos mostrar as duas formas, use a que for mais fácil para você.

Primeiro vamos mostrar a solução com subquery. Veja a sentença abaixo:

SELECT
segmento,
produto,
valor
FROM
(
SELECT segmento, produto, SUM(valor) valor
FROM tb_fornecedor
GROUP BY segmento, produto
UNION ALL
SELECT segmento, NULL AS  produto, SUM(valor) valor
FROM tb_fornecedor
GROUP BY segmento
UNION ALL
SELECT NULL AS segmento, NULL AS produto, SUM(valor) AS valor
FROM tb_fornecedor
) CALCULO
ORDER BY segmento, produto;

Nesta consulta, criamos uma subquery chamada CALCULO, o conteúdo da subquery está entre parenteses. A subquery contém três comandos "SELECT" com a seguinte função:

• o primeiro "SELECT" agrupa a soma por segmento e produto;
• o segundo "SELECT" agrupa a soma por segmento;
• o terceiro  "SELECT" calcula a soma de todos os produtos;

Repare que os registros retornados pelos comandos "SELECT" serão unidos através do comando "UNION ALL";

Observe também que os comandos "SELECT" da subquery foram intercalados com valores nulos "NULL", para que a quantidade de colunas fossem as mesmas em cada consulta.

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

Agora vamos mostrar a solução utilizando o comando "WITH". Veja a sentença abaixo:

WITH CALCULO AS
(
SELECT segmento, produto, SUM(valor) valor
FROM tb_fornecedor
GROUP BY segmento, produto
UNION
SELECT segmento, NULL AS  produto, SUM(valor) valor
FROM tb_fornecedor
GROUP BY segmento
UNION
SELECT NULL AS segmento, NULL AS produto, SUM(valor) AS valor
FROM tb_fornecedor
)
SELECT
segmento,
produto,
valor
FROM calculo
ORDER BY segmento, produto;

2º Exemplo

Para facilitarmos a interpretação do resultado da consulta vamos indicar o "Total Geral" e o "Subtotal" na consulta.

Solução

• Total Geral: colocaremos a indicação de "Total Geral" na primeira coluna, onde o valor do registro da primeira coluna apresentar valor nulo. Para fazer isso, vamos utilizar o comando "COALESCE";
• Subtotal: colocaremos a indicação de "Subtotal" na segunda coluna, onde o valor do registro da primeira coluna for diferente de nulo e o valor da segunda coluna for nulo. Para fazer isso, utilizamos o comando "CASE WHEN";

Temos duas formas de fazer esta consulta, podemos utilizar "subquery" ou o comando "WITH", vamos mostrar as duas formas, use a que for mais fácil para você.

Primeiro vamos mostrar a solução com subquery. Veja a sentença abaixo:

SELECT
COALESCE(segmento, 'TOTAL GERAL')  AS segmento,  
CASE 
WHEN(segmento IS NOT NULL AND produto IS NOT NULL) THEN produto 
WHEN(segmento IS NOT NULL AND produto IS NULL) THEN 'SUBTOTAL' || ' - '|| segmento
END AS produto,
valor
FROM
(
 SELECT segmento, produto, SUM(valor) valor
 FROM tb_fornecedor
 GROUP BY segmento, produto
 UNION
 SELECT segmento, NULL AS  produto, SUM(valor) valor
 FROM tb_fornecedor
 GROUP BY segmento
 UNION
 SELECT NULL AS segmento, NULL AS produto, SUM(valor) AS valor
 FROM tb_fornecedor
) CALCULO
ORDER BY
CASE
  WHEN(segmento IS NOT NULL) THEN CAST(0 AS INTEGER)
  ELSE CAST(1 AS INTEGER)
END
, segmento,
CASE
  WHEN(produto IS NOT NULL) THEN CAST(0 AS INTEGER)
  ELSE CAST(1 AS INTEGER)
END,
produto;

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

Observação: perceba que no "ORDER BY" utilizamos duas vezes o "CASE WHEN":

• 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;

Agora vamos mostrar a solução utilizando o comando "WITH". Veja a sentença abaixo:

WITH CALCULO AS
(
 SELECT segmento, produto, SUM(valor) valor
 FROM tb_fornecedor
 GROUP BY segmento, produto
 UNION
 SELECT segmento, NULL AS  produto, SUM(valor) valor
 FROM tb_fornecedor
 GROUP BY segmento
 UNION
 SELECT NULL AS segmento, NULL AS produto, SUM(valor) AS valor
 FROM tb_fornecedor
)
SELECT
COALESCE(segmento, 'TOTAL GERAL') AS segmento,  
CASE 
WHEN(segmento IS NOT NULL AND produto IS NOT NULL) THEN produto 
WHEN(segmento IS NOT NULL AND produto IS NULL) THEN 'SUBTOTAL' || ' - '|| segmento
END AS produto,
valor
FROM calculo
ORDER BY
CASE
  WHEN(segmento IS NOT NULL) THEN CAST(0 AS INTEGER)
  ELSE CAST(1 AS INTEGER)
END,
segmento,
CASE
  WHEN(produto IS NOT NULL) THEN CAST(0 AS INTEGER)
  ELSE CAST(1 AS INTEGER)
END,
produto;

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