Como calcular o total acumulado com SQL
Veja como fazer uma soma cumulativa utilizando SQL
Neste artigo vamos ver como fazer um total cumulativo no SQL. Nosso objetivo e saber qual o valor acumulado por período e qual o percentual que ele equivale ao total dos registros selecionados. Vamos ver como conseguir esse resultado utilizando MySQL e SQL Server. O resultado a ser atingido deve ser igual ao da imagem a seguir.
Para chegar nestes resultados, podemos fazer de duas formas.
1 - Utilizando subconsultas
2 - Utilizando Common Table Expressions e OVER .... PARTITION
Antes de começar, vamos criar a tabela e os dados que vamos utilizar neste artigo.
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 - Utilizando subconsultas
Se a nossa necessidade fosse saber o total de vendas por mês, bastaria fazer um agrupamento dos resultados por periodo e somar a coluna de vendas. Nossa consulta ficaria assim.
SELECT Periodo, SUM(Vendas) as Total FROM VendasDepartamento
GROUP BY Periodo
ORDER BY Periodo
Um de nossos primeiros objetivos é calcular qual o percentual que o total de vendas de um determinado mês tem em relação com o total geral. Para isso, será necessário pegar a soma acumulada do mês atual e dividir pela soma de todas as vendas da tabela. Para obter a soma de todas as vendas será necessário fazer uma subconsulta.
SELECT Periodo, SUM(Vendas) as Total, (SUM(Vendas) / (SELECT SUM(Vendas)
FROM VendasDepartamento))*100 as Percentual_ano
FROM VendasDepartamento
GROUP BY Periodo
ORDER BY Periodo
Agora vamos ver como calcular o total acumulado. Desejamos que o total seja calculado mês a mês. Para isso, precisamos fazer uma subconsulta que faça a soma das vendas onde o periodo seja igual ou menor do que o registro atual.
SELECT Periodo, SUM(Vendas) as Total,
(SUM(Vendas) / (SELECT SUM(Vendas) FROM VendasDepartamento))*100 as Percentual_ano,
(SELECT SUM(Vendas) FROM VendasDepartamento WHERE Periodo <= V.Periodo) as Total_acumulado
FROM VendasDepartamento as V
GROUP BY Periodo
ORDER BY Periodo
Caso você queira segmentar os resultados por apenas 1 departamento, basta fazer o filtro utilizando uma cláusula WHERE. No exemplo a seguir vamos filtrar os resultados pelo departamento de Informática, será necessário ajustar os subselects para considerar apenas este departamento
SELECT Periodo, SUM(Vendas) as Total,
(SUM(Vendas) / (SELECT SUM(Vendas) FROM VendasDepartamento WHERE Departamento = V.Departamento))*100 as Percentual_ano,
(SELECT SUM(Vendas) FROM VendasDepartamento WHERE Periodo <= V.Periodo and Departamento = V.Departamento) as Total_acumulado
FROM
VendasDepartamento as V
WHERE Departamento = "Informática"
GROUP BY Periodo, Departamento
Apesar de ter funcionado, as subconsultas deixam a leitura de nossa consulta mais complicada. Felizmente existe uma forma de simplificar este processo.
2 - Utilizando Common Table Expressions e OVER .... PARTITION
Commom Table Expression são resultados temporários que existem apenas no contexto atual. Sua sintaxe é
WITH <> AS (<Cláusula SELECT com os dados desejados>)
Vamos ver um exemplo simples
with CTE as (
SELECT Periodo, SUM(Vendas) as Total
FROM VendasDepartamento
GROUP BY Periodo
)
SELECT * from CTE;
Até aqui, nada de mais. Perceba que a nossa consulta foi transformada numa tabela chamada CTE. Essa tabela está apenas na memória, não foi criada no banco. Se você abrir outra aba em seu editor de consultas um SELECT na tabela CTE não deve funcionar. Ela existe apenas no contexto atual.
Com base nessa tabela temporária, podemos simplificar muito nosso trabalho. Vamos começar pela porcentagem do periodo em relação ao todo.
with CTE as (
SELECT Periodo, SUM(Vendas) as Total
FROM VendasDepartamento
GROUP BY Periodo
)
select
Periodo,
Total,
(Total / sum(Total) over())* 100 as Percentual_ano
from CTE
GROUP BY Periodo, Total
O percentual do ano é obtido pela divisão do total do periodo atual pelo total da tabela. Perceba que para pegar o total da tabela não foi necessário utilizar uma subconsulta. Utilizamos a cláusula OVER. Ela permite segmentar dados de acordo com um agrupamento informado como parâmetro. Como não informamos um parâmetro, ele considerou a tabela inteira.
A sintaxe da clausula OVER é a seguinte
<<Expressão de agrupamento>> OVER (PARTITION BY <> ORDER BY <>)
No nosso exemplo anterior, não utilizamos o PARTITION e o ORDER BY. Antes do OVER sempre vai existir uma expressão de agrupamento, em nosso caso foi uma soma.
Agora vamos ver como obter o total acumulado. O total acumulado é a soma das vendas até o periodo do registro atual.
with CTE as (
SELECT Periodo, SUM(Vendas) as Total
FROM VendasDepartamento
GROUP BY Periodo
)
select
Periodo,
Total,
(Total / sum(Total) over())* 100 as Percentual_ano,
sum(Total) over (order by Periodo) as Total_acumulado
from CTE
GROUP BY Periodo, Total
Perceba que no caso do total acumulado, nós informamos um parâmetro no OVER que foi o ORDER BY Periodo. Isso fez com que a soma acumulado fosse dos registros iguais ou anteriores ao periodo do registro atual.
Para concluir, vamos ver como ficaria a nossa consulta filtrando por apenas um departamento. Neste caso, seria necessário apenas um ajuste na consulta usada para criar a nossa Commom Table Expression. A forma de consultar a nossa CTE não mudaria
with CTE as (
SELECT Periodo, SUM(Vendas) as Total
FROM VendasDepartamento
WHERE Departamento = "Informática"
GROUP BY Periodo
)
select
Periodo,
Total,
(Total / sum(Total) over())* 100 as Percentual_ano,
sum(Total) over (order by Periodo) as Total_acumulado
from CTE
GROUP BY Periodo, Total