Criando totais e subtotais com agrupamento no SQL
Veja como criar totais e subtotais para seus resultados no SQL
Neste artigo vamos ver como criar totais e subtotais para nossos resultados no SQL. Os exemplos que vamos ver podem ser executados no MySQL e no SQL Server.
Para fins de relatórios, quando é necessário gerar totais e subtotais, utilizamos a cláusula ROLLUP. O ROLLUP é utilizado junto com o agrupamento. As colunas informadas são utilizadas para agrupar os subtotais, e no final da consulta você vai ter uma linha com o resultado final.
O que você vai aprender neste artigo?
1 - Criando totais.
2 - Criando subtotais.
Vamos começar criando os dados que serão utilizados.
MySQL
CREATE TABLE VendasDepartamento(
Periodo date NULL,
Departamento varchar(50) NULL,
Vendas decimal(18, 2) NULL
)
SQL Server
CREATE TABLE VendasDepartamento (
Periodo date,
Departamento varchar(50),
Vendas float
);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-01-01", "Informática", 500);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-01-01", "Móveis", 700);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-01-01", "Ferramentas", 300);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-02-01", "Informática", 900);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-02-01", "Móveis", 300);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-02-01", "Ferramentas", 100);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-03-01", "Informática", 580);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-03-01", "Móveis", 720);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-03-01", "Ferramentas", 350);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-04-01", "Informática", 510);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-04-01", "Móveis", 720);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-04-01", "Ferramentas", 330);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-05-01", "Informática", 900);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-05-01", "Móveis", 1700);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-05-01", "Ferramentas", 900);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-06-01", "Informática", 400);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-06-01", "Móveis", 200);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-06-01", "Ferramentas", 400);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-07-01", "Informática", 590);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-07-01", "Móveis", 790);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-07-01", "Ferramentas", 390);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-08-01", "Informática", 510);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-08-01", "Móveis", 910);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-08-01", "Ferramentas", 510);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-09-01", "Informática", 1200);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-09-01", "Móveis", 1700);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-09-01", "Ferramentas", 1300);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-10-01", "Informática", 540);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-10-01", "Móveis", 710);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-10-01", "Ferramentas", 320);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-11-01", "Informática", 590);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-11-01", "Móveis", 770);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-11-01", "Ferramentas", 310);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-12-01", "Informática", 560);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-12-01", "Móveis", 900);
INSERT INTO VendasDepartamento (Periodo, Departamento, Vendas) VALUES ("2021-12-01", "Ferramentas", 410);
1 - Calculando os totais
Para obter o valor total de todos os registros de nossa tabela poderiamos fazer da seguinte forma:
SELECT sum(Vendas) as Total from vendasdepartamento
Vamos agrupar a soma das vendas por período e vamos acrescentar este resultado no final de nossa consulta. Para isso, vamos utilizar o ROLLUP
SELECT
Periodo, sum(Vendas) as Total FROM vendasdepartamento
GROUP BY Periodo WITH ROLLUP
Perceba que foi acrescentada uma linha no final onde a coluna Periodo está com o valor NULL e ao lado temos o total de toda a tabela.
Para substituir o NULL por um rótulo a nossa escolha, vamos ter que utilizar COALESCE. O COALESCE vai permitir substituir o valor nulo por um texto a nossa escolha. Como o campo período é um campo do tipo data, será necessário utilizar CAST para converter o campo em nchar.
SELECT
COALESCE (cast(Periodo as NCHAR), 'Total Geral') as Periodo,
sum(Vendas) as Total FROM vendasdepartamento
GROUP BY Periodo WITH ROLLUP
2 - Calculando os subtotais
Agora vamos gerar os subtotais. Como vimos no primeiro exemplo, estamos agrupando os resultados pela coluna Periodo. Vamos agora fazer um agrupamento por Periodo e Departamento, e gerar os subtotais.
SELECT
COALESCE (cast(Periodo as NCHAR), 'Total Geral') as Periodo,
Departamento, sum(Vendas) as Total FROM vendasdepartamento
GROUP BY Periodo, Departamento WITH ROLLUP
Para cada total de periodo existe uma linha onde a coluna Departamento está com o valor NULL. Da mesma forma que fizemos com o exemplo anterior, vamos utilizar COALESCE para substituir o valor nulo por um rótulo a nossa escolha.
SELECT
COALESCE (cast(Periodo as NCHAR), 'Total Geral') as Periodo,
COALESCE(Departamento, 'Total Periodo') AS Departamento,
sum(Vendas) as Total FROM vendasdepartamento
GROUP BY Periodo, Departamento WITH ROLLUP