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.