Criando totais e subtotais com agrupamento no SQL

Criando totais e subtotais com agrupamento 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

 

Outros conteudos que podem ser de seu interesse

Máscaras para CPF, CNPJ e outros tipos de dados com SQL
30/08/2020SQL

Máscaras para CPF, CNPJ e outros tipos de dados com SQL

Neste artigo você vai ver exemplos de como aplicar máscaras em dados nos banco de dados SQL Server e MySQL

Saiba mais...
Criando uma tabela calendário com SQL
04/02/2024SQL

Criando uma tabela calendário com SQL

Aprenda como criar uma tabela calendário no SQL para controlar dias úteis e feriados

Saiba mais...

Conteúdo sobre banco de dados sem complicação!