Tecnologia da Informação

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.

- 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