Como calcular o total acumulado com SQL

Como calcular o total acumulado com 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.

 

Total acumulado SQL

 

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 <<nome_do_CTE>> AS (<<SELECT com os critérios 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 <<nomes dos campos>> ORDER BY <<campo>>)

 

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

 

 

Outros conteudos que podem ser de seu interesse

Localizando tabela por data de criação com SQL Server ou MySQL
08/08/2016SQL

Localizando tabela por data de criação com SQL Server ou MySQL

Uma dica rápida para quem trabalha com SQL Server ou MySQL e deseja localizar tabelas por data de criação

Saiba mais...
SQL vs Mongo - expressões regulares
22/12/2019SQL

SQL vs Mongo - expressões regulares

Veja como utilizar expressões regulares no SQL e no Mongo

Saiba mais...

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