Como utilizar totalizadores para consultas em SQL SERVER utilizando-se somente um select.

Muitas vezes precisamos de totalizadores para uma consulta, o que nos obriga a utilizar duas consultas, procedures, views, cursores sendo que em muitas situações conseguimos resolver com funções desconhecidas como ROLLUP e CUBE utililzadas juntamente com o operador GROUP BY. O rollup deve ser usando em conjunto com o group by e pode ser usado com order by para ordenação. No final dos resultados (ou no inicio dependendo da ordenação) será exibido o totalizador(soma)  com um campo de referencia nula (null); alem disso, caso o group by tenha mais de um campo, as somas parciais do agrupamento serão geradas com base somente no primeiro campo do agrupamento e será exibido também a soma geral. Há também o totalizador CUBE, onde sua diferença em relação ao ROLLUP é que ele gera todas combinações possíveis com os campos contidos no GROUP BY. Conforme dito anteriormente, os totalizadores geram linhas com algum campo nulo; para saber se o campo nulo foi gerado pela função ROLLUP ou CUBE basta usar a função grouping(campo) onde    será criada uma coluna adicional no formato de bits (0 e 1). Onde for 0 é retorno de sua consulta, e onde constar 1 é retorno das funções. Supondo uma tabela chamada "estoque" com três campos inteiros: CODPRODUTO (CODIGO DO PRODUTO) CODFORNECEDOR (CODIGO DO FORNECEDOR) QUANTIDADE (QUANTIDADE DE DETERMINADO PRODUTO E DETERMINADO FORNECEDOR) Script para criação: CREATE TABLE [dbo].[estoque]( [codProduto] [int] NOT NULL, [codFornecedor] [int] NOT NULL, [quantidade] [int] NOT NULL ) A TABELA ESTÁ PREENCHIDA ASSIM: (Respectivamente: codProduto, codFornecedor, quantidade) 1 1 10 1 2 20 2 3 50 1 4 20 3 5 7 4 6 25 Supondo uma consulta simples de quantos produtos tenho em estoque: select codProduto, sum(quantidade) as qtde from estoque group by codProduto resultado: 1 50 2 50 3 7 4 25 Mas e se eu quiser saber das informações individuais, quantos produtos tenho em estoque no total? Facilmente podemos resolver utilizando o operador ROLLUP: select codProduto, sum(quantidade) as qtde from estoque group by codProduto WITH ROLLUP Resultado: 1 50 2 50 3 7 4 25 NULL 132 Note que o nulo gerado na ultima linha contém a soma dos outros registros. Agora supondo que eu queira saber a quantidade de cada produto mas quero saber por fornecedor: select codProduto, codfornecedor, sum(quantidade) as qtde from estoque group by codProduto, codfornecedor with rollup Resultado: 1 1 10 1 2 20 1 4 20 1 NULL 50 2 3 50 2 NULL 50 3 5 7 3 NULL 7 4 6 25 4 NULL 25 NULL NULL 132 Nesse caso o ROLLUP mostra também as somas parciais: onde : 1 NULL 50 nada mais é que a soma das 3 primeiras linhas. Ele mostra o 1 que é o agrupamento, e 50 que é a soma. Além disso no final onde está: NULL NULL 132 é a soma geral. Lembrando que no ROLLUP somente serão mostradas as somas parciais do primeiro campo do group by e também a soma geral. A diferença do CUBE para o ROLLUP é que o CUBE gera todas combinações possíveis. Supondo o mesmo exemplo onde seja necessário pegar a quantidade de cada produto por fornecedora só que utilizando CUBE: select codProduto,codfornecedor , sum(quantidade) as qtde from estoque group by codProduto, codFornecedor WITH cube 1 1 10 1 2 20 1 4 20 1 NULL 50 2 3 50 2 NULL 50 3 5 7 3 NULL 7 4 6 25 4 NULL 25 NULL NULL 132 NULL 1 10 NULL 2 20 NULL 3 50 NULL 4 20 NULL 5 7 NULL 6 25 note que além do mesmo resultado utilizando-se CUBE apareceram novas linhas: NULL 1 10 NULL 2 20 NULL 3 50 NULL 4 20 NULL 5 7 NULL 6 25 essas linhas, são as somas parcias do segundo campo do group by; o cube faz todas as combinações possíveis do campo group by. Para finalizar, e saber se os nulos são dados do banco ou resultado dos totalizadores, supondo o ultimo exemplo utilizando ROLLUP: select codProduto, GROUPING(CODPRODUTO) ,codfornecedor, GROUPING(CODFORNECEDOR),  sum(quantidade) as qtde from estoque group by codProduto, codfornecedor with rollup Nesse caso adicionei dois campos (funções) à consulta: GROUPING(CODPRODUTO) GROUPING(CODFORNECEDOR) O resultado será: 1 0 1 0 10 1 0 2 0 20 1 0 4 0 20 1 0 NULL 1 50 2 0 3 0 50 2 0 NULL 1 50 3 0 5 0 7 3 0 NULL 1 7 4 0 6 0 25 4 0 NULL 1 25 NULL 1 NULL 1 132 note que na frente de cada campo nulo, tem um (1) que mostra que o nulo é resultado nesse caso da função ROLLUP. Se ao invés de 1 fosse 0, então os nulos seriam valores retornados da tabela em questão. Abraços. Att, Paulo Daniel Bandeira de Mello paulo@seven.com.br

Quebra-Linha

Colaborador..: Paulo Daniel Bandeira de Mello
Categoria(s).: Microsoft SQL Server;
Versão.......: 1.0
Data.........: 09/08/2007 10:16:29
Visualizado..: 7096 vezes
Fonte........: .