[Resolvido] Gerando Somatórios

[eriva_br 7]

totalizando, somando dados:

 

no SQL Server, para estas operações de somar e totalizar podemos usar as seguintes funções:

- COMPUTE: realiza somatórios, nos agrupamentos divide os grupos em result-sets diferentes.

Podem ser usados outros operadores no compute: { AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM }

 

- ROLLUP: realiza somatórios, nos agrupamentos mantém em um único result-sets

 

- CUBE: realiza somatórios, nos agrupamentos mantém em um único result-sets e no final gera totais por grupos

 

 

Dados para testes:

set nocount ondeclare @tab table (IDProduto char(5), Nome varchar(10), valor money)insert into @tab (IDProduto, Nome, valor) values (1, 'Norte', 10)insert into @tab (IDProduto, Nome, valor) values (1, 'Sul', 20)insert into @tab (IDProduto, Nome, valor) values (2, 'Norte', 5)insert into @tab (IDProduto, Nome, valor) values (2, 'Norte', 15)insert into @tab (IDProduto, Nome, valor) values (2, 'Norte', 25)insert into @tab (IDProduto, Nome, valor) values (1, 'Leste', 10)insert into @tab (IDProduto, Nome, valor) values (3, 'Oeste', 15)insert into @tab (IDProduto, Nome, valor) values (3, 'Oeste', 5)insert into @tab (IDProduto, Nome, valor) values (3, 'Norte', 5)

 

Exemplo com COMPUTE:

select IDProduto, Nome, valor from @tab		  order by IDProduto		  compute sum(valor) by IDProduto		  compute sum(valor)

resultado da query acima:

                  IDProduto Nome       valor				 		  --------- ---------- --------------------- 		  1		 Norte	  10.0000		  1		 Sul	  20.0000		  1		 Leste	  10.0000		  							   sum							   =====================							   40.0000		  		  		  IDProduto Nome	   valor				 		  --------- ---------- --------------------- 		  2		 Norte	  25.0000		  2		 Norte	  5.0000		  2		 Norte	  15.0000		  							   sum							   =====================							   45.0000		  		  		  IDProduto Nome	   valor				 		  --------- ---------- --------------------- 		  3		 Oeste	  15.0000		  3		 Oeste	  5.0000		  3		 Norte	  5.0000		  							   sum							   =====================							   25.0000		  		  							   sum							   =====================							   110.0000

Exemplo com ROLLUP:

select 			 case when (grouping(IDProduto)=1) then 'TOTAL'			 else isnull(IDProduto, 'desconhecido') end as IDProduto, 			 case when (grouping(Nome)=1) then 'SUB-TOTAL'			 else isnull(Nome, 'desconhecido') end as Nome,			 sum(valor) as valor 		 from @tab		 group by IDProduto, Nome 		 WITH ROLLUP

resultado da query acima:

                IDProduto Nome	     valor				 		--------- ---------- --------------------- 		1		 Leste	  10.0000		1		 Norte	  10.0000		1		 Sul	  20.0000		1		 SUB-TOTAL  40.0000		2		 Norte	  45.0000		2		 SUB-TOTAL  45.0000		3		 Norte	  5.0000		3		 Oeste	  20.0000		3		 SUB-TOTAL  25.0000		TOTAL	 SUB-TOTAL  110.0000

Exemplo com CUBE:

select 			case when (grouping(IDProduto)=1) then 'TOTAL'			else isnull(IDProduto, 'desconhecido') end as IDProduto, 			case when (grouping(Nome)=1) then 'SUB-TOTAL'			else isnull(Nome, 'desconhecido') end as Nome,			sum(valor) as valor 		from @tab		group by IDProduto, Nome 		WITH CUBE

resultado da query acima:

                IDProduto Nome	     valor				 		--------- ---------- --------------------- 		1		 Leste	  10.0000		1		 Norte	  10.0000		1		 Sul	  20.0000		1		 SUB-TOTAL  40.0000		2		 Norte	  45.0000		2		 SUB-TOTAL  45.0000		3		 Norte	  5.0000		3		 Oeste	  20.0000		3		 SUB-TOTAL  25.0000		TOTAL	 SUB-TOTAL  110.0000		TOTAL	 Leste	  10.0000		TOTAL	 Norte	  60.0000		TOTAL	 Oeste	  20.0000		TOTAL	 Sul		20.0000

no exemplo podemos observar tb. o uso da função GROUPING, com esta função verificamos se o retorno refere-se a uma linha de sub-totais ou a uma linha de totais

 

 

AUTOR: "eriva_br"

 

Dúvidas, criticas, contribuições, correções e adições seram bem vindas.