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.

MySql - SUM COM ROLLUP - Calcular o subtotal / total

Neste artigo, vamos mostrar 3 exemplos de como utilizar a função "SUM" acompanhada do comando "WITH ROLLUP" para calcular o subtotal / total em uma consulta.
Caso ainda não conheça a função "SUM", veja o artigo "Mysql SUM - Soma "

Caso tenha interesse, faça o download ou veja o script deste artigo no github.
O script também está disponível para execução no Sql Fiddle e foi testado no Mysql 5.1.61.

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 "WITH 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  segmento, produto WITH ROLLUP;

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
    IF
    (
      segmento IS NULL,
      'Total Geral',
      IF (produto IS NULL, CONCAT('Subtotal - ', segmento), segmento)
    )
    AS segmento,
    produto,
    SUM(valor) as valor
FROM tb_fornecedor
GROUP BY  segmento, produto WITH ROLLUP;

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

• Utilizamos a condição "IF" para verificar onde o segmento era nulo e substituímos por "Total Geral";
• Para segmento não nulo fizemos uma outra verificação. Foi encadeado um outro "IF" para verificar onde o produto era nulo e substituímos por "Subtotal" acompanhado do  nome do segmento, caso o produto não fosse nulo mantivemos o nome do segmento;
• Observação: utilizamos a função "CONCAT" para unir a palavra "Subtotal" ao conteúdo da coluna segmento;

3º  Exemplo

Não é possível utilizar "WITH ROLLUP" com "ORDER BY" na mesma consulta. Neste exemplo, vamos mostrar como ordenar colunas utilizando uma subquery (subconsulta). Vamos ordenar as colunas "segmento" e "produto" em ordem decrescente.

Solução

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, 
 produto, 
 SUM(valor) AS valor
 FROM tb_fornecedor
 GROUP BY segmento, produto WITH ROLLUP
) TOTAL
ORDER BY
CASE
 WHEN(segmento IS NOT NULL) THEN 0
 ELSE 1
END,
segmento DESC,
CASE
 WHEN(produto IS NOT NULL) THEN 0
 ELSE 1
END,
produto DESC;

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

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;

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.