Tecnologia da Informação
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:
- 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...
- 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
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;
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
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":
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.
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";
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;
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;
Gostou? Siga no Google + ou Facebook.
loading...
- 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...
- 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...