Usando GROUP BY com ROLLUP, CUBE e GROUPING SETS

Os operadores ROLLUP, CUBE e GROUPING SETS são extensões da cláusula GROUP BY. Os operadores ROLLUP, CUBE ou GROUPING SETS podem gerar o mesmo conjunto de resultados como ao usar UNION ALL para combinar agrupamentos de consultas individuais; entretanto, o uso de um operador GROUP BY normalmente é mais eficiente.

O operador GROUPING SETS pode gerar o mesmo conjunto de resultados gerado por uma operação simples de GROUP BY, ROLLUP ou CUBE. Quando todos os agrupamentos gerados pelo uso de operadores ROLLUP ou CUBE não são necessários, você pode usar GROUPING SETS para especificar somente os agrupamentos que quiser usar. A lista de GROUPING SETS pode conter agrupamentos duplicados e, ao usar GROUPING SETS com ROLLUP e CUBE, pode gerar agrupamentos duplicados. Agrupamentos duplicados são retidos como seriam se UNION ALL fosse usado.

Observação
CUBE, ROLLUP e GROUPING SETS não dão suporte à função CHECKSUM_AGG.

Elementos compostos e concatenados

Várias colunas que estão entre parênteses internos na lista GROUPING SETS são tratadas como um único conjunto. Por exemplo, na cláusula GROUP BY GROUPING SETS ((Colum1, Column2), Column3, Column4), Column1 e Column2 são tratadas como uma coluna. Para obter um exemplo de como usar GROUPING SETS com elementos compostos, consulte o exemplo H posteriormente neste tópico.

Quando a lista GROUPING SETS contém múltiplos conjuntos entre parênteses internos, separados por vírgulas, a saída dos conjuntos é concatenada. O conjunto de resultados é o produto cruzado ou produto cartesiano dos conjuntos de agrupamentos. Para obter um exemplo de como usar GROUP BY em operações ROLLUP concatenadas, consulte o exemplo D posteriormente neste tópico.

ROLLUP e CUBE comparados a Dimensões OLAP

Consultas que usam os operadores ROLLUP e CUBE geram alguns dos mesmos conjuntos de resultados e executam alguns dos mesmos cálculos que aplicativos OLAP. O operador CUBE gera um conjunto de resultados que pode ser usado para relatórios de tabulação cruzada. Uma operação de ROLLUP pode calcular o equivalente a uma dimensão ou hierarquia de OLAP.

Por exemplo, dada uma determinada dimensão de tempo com níveis ou atributos de ano, mês e dia, a operação ROLLUP gera os agrupamentos a seguir.

Operação	Agrupamentos
ROLLUP (DATEPART(yyyy,OrderDate) ,DATEPART(mm,OrderDate) ,DATEPART(dd,OrderDate))	year, month, day year, month year ()

Dada uma dimensão de local com os níveis região e cidade concatenados com os níveis de dimensão de tempo de ano, mês e dia, a operação ROLLUP gera os agrupamentos a seguir.

Operação	Agrupamentos
ROLLUP (region, city), ROLLUP (DATEPART(yyyy,OrderDate) ,DATEPART(mm,OrderDate) ,DATEPART(dd,OrderDate))	region, city, year, month, day region, city, year, month region, city, year region, city region, year, month, day region, year, month region, year region year, month, day year, month year ()

Uma operação CUBE com os mesmos níveis de dimensões de local e tempo gera os seguintes agrupamentos.

Operação

Agrupamento

CUBE (region, city ,DATEPART(yyyy,OrderDate) ,DATEPART(mm,OrderDate) ,DATEPART(dd,OrderDate))

region, city, year, month, day region, city, year, month region, city, year region, city region, city, month, day region, city, month region, city, day region, city, year, day region, city, day region, year, month, day region, year, month region, year region, month, day region, month region, year, day region, day region city, year, month, day city, year, month city, year city, month, day city, month city, year, day city, day year, month, day year, month year year, day month, day month day ()

NULL em conjuntos de resultados

Nos conjuntos de resultados gerados pelos operadores GROUP BY, NULL tem os seguintes usos:

• Se uma coluna de agrupamento contiver NULL, todos os valores nulos serão considerados iguais e colocados em um único grupo NULL.

• Quando uma coluna é agregada em uma linha, o valor da coluna é mostrado como NULL.

O exemplo a seguir usa a função GROUPING para mostrar os dois usos de NULL. UNKNOWN substitui NULL em linhas onde os nulos de uma coluna foram agrupados. ALL substitui NULL em uma coluna onde NULL indica que uma coluna foi incluída em uma agregação.

USE tempdb;
GO
CREATE TABLE dbo.GroupingNULLS (
    Store nvarchar(19)
    ,SaleYear nvarchar(4)
    ,SaleMonth nvarchar (7))
INSERT INTO dbo.GroupingNULLS VALUES
(NULL,NULL,'January')
,(NULL,'2002',NULL)
,(NULL,NULL,NULL)
,('Active Cycling',NULL ,'January')
,('Active Cycling','2002',NULL)
,('Active Cycling',NULL ,NULL)
,('Active Cycling',NULL,'January')
,('Active Cycling','2003','Febuary')
,('Active Cycling','2003',NULL)
,('Mountain Bike Store','2002','January')
,('Mountain Bike Store','2002',NULL)
,('Mountain Bike Store',NULL,NULL)
,('Mountain Bike Store','2003','January')
,('Mountain Bike Store','2003','Febuary')
,('Mountain Bike Store','2003','March');

SELECT ISNULL(Store,
    CASE WHEN GROUPING(Store) = 0 THEN 'UNKNOWN' ELSE 'ALL' END)
    AS Store
    ,ISNULL(CAST(SaleYear AS nvarchar(7)),
    CASE WHEN GROUPING(SaleYear)= 0 THEN 'UNKNOWN' ELSE 'ALL' END)
    AS SalesYear
    ,ISNULL(SaleMonth,
    CASE WHEN GROUPING(SaleMonth) = 0 THEN 'UNKNOWN' ELSE 'ALL'END)
    AS SalesMonth
    ,COUNT(*) AS Count
FROM dbo.GroupingNULLS 
GROUP BY ROLLUP(Store, SaleYear, SaleMonth);

Conjunto de resultados.

Loja	Vendas (ano)	Vendas (mês)	Contagem
Desconhecido	Desconhecido	Desconhecido	1
Desconhecido	Desconhecido	Janeiro	1
Desconhecido	Desconhecido	ALL	2
Desconhecido	2002	Desconhecido	1
Desconhecido	2002	ALL	1
Desconhecido	ALL	ALL	3
Ciclos ativos	Desconhecido	Desconhecido	1
Ciclos ativos	Desconhecido	Janeiro	2
Ciclos ativos	Desconhecido	ALL	3
Ciclos ativos	2002	Desconhecido	1
Ciclos ativos	2002	ALL	1
Ciclos ativos	2003	Desconhecido	1
Ciclos ativos	2003	Fevereiro	1
Ciclos ativos	2003	ALL	2
Ciclos ativos	ALL	ALL	6
Loja de mountain bike	Desconhecido	Desconhecido	1
Loja de mountain bike	Desconhecido	ALL	1
Loja de mountain bike	2002	Desconhecido	1
Loja de mountain bike	2002	Janeiro	1
Loja de mountain bike	2002	ALL	2
Loja de mountain bike	2003	Fevereiro	1
Loja de mountain bike	2003	Janeiro	1
Loja de mountain bike	2003	Março	1
Loja de mountain bike	2003	ALL	3
Loja de mountain bike	ALL	ALL	6
ALL	ALL	ALL	15

EXEMPLOS

Os exemplos desta seção usam a função de agregação SUM para comparar os conjuntos de resultados. As outras funções de agregação também poderiam ser usadas para calcular resumos diferentes.

A. Usando um simples GROUP BY

No exemplo a seguir, o simples GROUP BY retorna um conjunto de resultados para comparar aos conjuntos de resultados dos exemplos de B a K, que usam os operadores GROUP BY com a mesma instrução SELECT.

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID
ORDER BY T.[Group], T.CountryRegionCode
    ,S.Name,H.SalesPersonID;

Conjunto de resultados.

Região	País	Loja	ID do vendedor	Total de vendas
Europa	DE	Empresa de material esportivo Versátil	284	859.232
Europa	DE	Empresa de material esportivo Versátil	289	17691.83
Europa	FR	Spa e academias de ginástica	284	32774.36
Europa	FR	Spa e academias de ginástica	286	246272.4

B. Usando GROUP BY ROLLUP

No exemplo a seguir, o operador ROLLUP retorna um conjunto de resultados que contém os seguintes agrupamentos:

• Region, Country, Store e SalesPersonID

• Region, Country e Store

• Region e Country

• Region

• total geral

O número de agrupamentos gerados por ROLLUP é o mesmo que o número de colunas da lista ROLLUP mais um agrupamento de total geral. O número de linhas em um agrupamento é gerado pelo número de combinações exclusivas de valores nas colunas do agrupamento.

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales' 
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY ROLLUP(
    T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID)
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

Conjunto de resultados.

Região	País	Loja	ID do vendedor	Total de vendas
NULL	NULL	NULL	NULL	297597.8
Europa	NULL	NULL	NULL	297597.8
Europa	DE	NULL	NULL	18551.07
Europa	DE	Empresa de material esportivo Versátil	NULL	18551.07
Europa	DE	Empresa de material esportivo Versátil	284	859.232
Europa	DE	Empresa de material esportivo Versátil	289	17691.83
Europa	FR	NULL	NULL	279046.8
Europa	FR	Spa e academias de ginástica	NULL	279046.8
Europa	FR	Spa e academias de ginástica	284	32774.36
Europa	FR	Spa e academias de ginástica	286	246272.4

C. Usando GROUP BY ROLLUP com a ordem de coluna invertida

No exemplo a seguir, o operador ROLLUP retorna um conjunto de resultados que contém os seguintes agrupamentos:

• SalesPersonID, Store, Country e Region

• SalesPersonID, Store e Country

• SalesPersonID e Store

• SalesPersonID

• total geral

As colunas na lista ROLLUP são as mesmas do exemplo B, mas estão na ordem inversa. Colunas são totalizadas da direita para a esquerda; assim, a ordem afeta os agrupamentos. O número de linhas no conjunto de resultados poderia variar de acordo com a ordem da coluna.

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY ROLLUP(
    H.SalesPersonID, S.Name, T.CountryRegionCode, T.[Group])
ORDER BY H.SalesPersonID, S.Name, T.CountryRegionCode, T.[Group];

Conjunto de resultados.

Região	País	Loja	ID do vendedor	Total de vendas
NULL	NULL	NULL	NULL	297597.8
NULL	NULL	NULL	284	33633.59
NULL	NULL	Spa e academias de ginástica	284	32774.36
NULL	FR	Spa e academias de ginástica	284	32774.36
Europa	FR	Spa e academias de ginástica	284	32774.36
NULL	NULL	Empresa de material esportivo Versátil	284	859.232
NULL	DE	Empresa de material esportivo Versátil	284	859.232
Europa	DE	Empresa de material esportivo Versátil	284	859.232
NULL	NULL	NULL	286	246272.4
NULL	NULL	Spa e academias de ginástica	286	246272.4
NULL	FR	Spa e academias de ginástica	286	246272.4
Europa	FR	Spa e academias de ginástica	286	246272.4
NULL	NULL	NULL	289	17691.83
NULL	NULL	Empresa de material esportivo Versátil	289	17691.83
NULL	DE	Empresa de material esportivo Versátil	289	17691.83
Europa	DE	Empresa de material esportivo Versátil	289	17691.83

D. Usando GROUP BY com operações ROLLUP concatenadas

No exemplo a seguir, o produto cruzado de duas operações ROLLUP é retornado.

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,DATEPART(yyyy,OrderDate) AS 'Year'
    ,DATEPART(mm,OrderDate) AS 'Month'
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND DATEPART(yyyy,OrderDate) = '2004'
GROUP BY 
    ROLLUP(T.[Group], T.CountryRegionCode)
    ,ROLLUP(DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate))
ORDER BY T.[Group], T.CountryRegionCode
    ,DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate);

Conjunto de resultados.

Região	País	Ano	Mês	Total de vendas
NULL	NULL	NULL	NULL	3031201
NULL	NULL	2004	NULL	3031201
NULL	NULL	2004	1	208553.6
NULL	NULL	2004	2	819466.6
NULL	NULL	2004	3	298579.1
NULL	NULL	2004	4	294427.7
NULL	NULL	2004	5	1070679
NULL	NULL	2004	6	339495.1
Europa	NULL	NULL	NULL	3031201
Europa	NULL	2004	NULL	3031201
Europa	NULL	2004	1	208553.6
Europa	NULL	2004	2	819466.6
Europa	NULL	2004	3	298579.1
Europa	NULL	2004	4	294427.7
Europa	NULL	2004	5	1070679
Europa	NULL	2004	6	339495.1
Europa	DE	NULL	NULL	1196260
Europa	DE	2004	NULL	1196260
Europa	DE	2004	1	155066.2
Europa	DE	2004	2	197801.8
Europa	DE	2004	3	180977.7
Europa	DE	2004	4	222683.4
Europa	DE	2004	5	258962
Europa	DE	2004	6	180769.1
Europa	FR	NULL	NULL	1834941
Europa	FR	2004	NULL	1834941
Europa	FR	2004	1	53487.37
Europa	FR	2004	2	621664.9
Europa	FR	2004	3	117601.4
Europa	FR	2004	4	71744.28
Europa	FR	2004	5	811716.9
Europa	FR	2004	6	158726

E. Usando GROUP BY CUBE

No exemplo a seguir, o operador CUBE retorna um conjunto de resultados que contém um agrupamento para todas as combinações possíveis de coluna na lista CUBE e um total geral de agrupamentos.

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY CUBE(
    T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID)
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

Conjunto de resultados.

Região	País	Loja	ID do vendedor	Total de vendas
NULL	NULL	NULL	NULL	297597.8
NULL	NULL	NULL	284	33633.59
NULL	NULL	NULL	286	246272.4
NULL	NULL	NULL	289	17691.83
NULL	NULL	Spa e academias de ginástica	NULL	279046.8
NULL	NULL	Spa e academias de ginástica	284	32774.36
NULL	NULL	Spa e academias de ginástica	286	246272.4
NULL	NULL	Empresa de material esportivo Versátil	NULL	18551.07
NULL	NULL	Empresa de material esportivo Versátil	284	859.232
NULL	NULL	Empresa de material esportivo Versátil	289	17691.83
NULL	DE	NULL	NULL	18551.07
NULL	DE	NULL	284	859.232
NULL	DE	NULL	289	17691.83
NULL	DE	Empresa de material esportivo Versátil	NULL	18551.07
NULL	DE	Empresa de material esportivo Versátil	284	859.232
NULL	DE	Empresa de material esportivo Versátil	289	17691.83
NULL	FR	NULL	NULL	279046.8
NULL	FR	NULL	284	32774.36
NULL	FR	NULL	286	246272.4
NULL	FR	Spa e academias de ginástica	NULL	279046.8
NULL	FR	Spa e academias de ginástica	284	32774.36
NULL	FR	Spa e academias de ginástica	286	246272.4
Europa	NULL	NULL	NULL	297597.8
Europa	NULL	NULL	284	33633.59
Europa	NULL	NULL	286	246272.4
Europa	NULL	NULL	289	17691.83
Europa	NULL	Spa e academias de ginástica	NULL	279046.8
Europa	NULL	Spa e academias de ginástica	284	32774.36
Europa	NULL	Spa e academias de ginástica	286	246272.4
Europa	NULL	Empresa de material esportivo Versátil	NULL	18551.07
Europa	NULL	Empresa de material esportivo Versátil	284	859.232
Europa	NULL	Empresa de material esportivo Versátil	289	17691.83
Europa	DE	NULL	NULL	18551.07
Europa	DE	NULL	284	859.232
Europa	DE	NULL	289	17691.83
Europa	DE	Empresa de material esportivo Versátil	NULL	18551.07
Europa	DE	Empresa de material esportivo Versátil	284	859.232
Europa	DE	Empresa de material esportivo Versátil	289	17691.83
Europa	FR	NULL	NULL	279046.8
Europa	FR	NULL	284	32774.36
Europa	FR	NULL	286	246272.4
Europa	FR	Spa e academias de ginástica	NULL	279046.8
Europa	FR	Spa e academias de ginástica	284	32774.36
Europa	FR	Spa e academias de ginástica	286	246272.4

F. Usando CUBE com elementos compostos

No exemplo a seguir, o operador CUBE retorna um conjunto de resultados que contém um agrupamento para todas as possíveis combinações de coluna na lista CUBE e um total geral de agrupamentos.

O operador processa as colunas agrupadas (T.[Group], T.CountryRegionCode) e (DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)), cada uma como uma única coluna.

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,DATEPART(yyyy,OrderDate) AS 'Year'
    ,DATEPART(mm,OrderDate) AS 'Month'
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND DATEPART(yyyy,OrderDate) = '2004'
GROUP BY CUBE(
    (T.[Group], T.CountryRegionCode)
    ,(DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)))
ORDER BY T.[Group], T.CountryRegionCode
    ,DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate);

Conjunto de resultados.

Região	País	Ano	Mês	Total de vendas
NULL	NULL	NULL	NULL	3031201
NULL	NULL	2004	1	208553.6
NULL	NULL	2004	2	819466.6
NULL	NULL	2004	3	298579.1
NULL	NULL	2004	4	294427.7
NULL	NULL	2004	5	1070679
NULL	NULL	2004	6	339495.1
Europe	DE	NULL	NULL	1196260
Europa	DE	2004	1	155066.2
Europa	DE	2004	2	197801.8
Europa	DE	2004	3	180977.7
Europa	DE	2004	4	222683.4
Europa	DE	2004	5	258962
Europa	DE	2004	6	180769.1
Europa	FR	NULL	NULL	1834941
Europa	FR	2004	1	53487.37
Europa	FR	2004	2	621664.9
Europa	FR	2004	3	117601.4
Europa	FR	2004	4	71744.28
Europa	FR	2004	5	811716.9
Europa	FR	2004	6	158726

G. Usando GROUP BY com GROUPING SETS

No exemplo a seguir, o operador GROUPING SETS possui quatro agrupamentos, um para cada coluna da lista SELECT. O operador retorna uma linha para cada valor exclusivo das colunas Region, Country, Store, e SalesPersonID .

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY GROUPING SETS
    (T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID)
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

Conjunto de resultados.

Região	País	Loja	ID do vendedor	Total de vendas
NULL	NULL	NULL	284	33633.59
NULL	NULL	NULL	286	246272.4
NULL	NULL	NULL	289	17691.83
NULL	NULL	Spa e academias de ginástica	NULL	279046.8
NULL	NULL	Empresa de material esportivo Versátil	NULL	18551.07
NULL	DE	NULL	NULL	18551.07
NULL	FR	NULL	NULL	279046.8
Europa	NULL	NULL	NULL	297597.8

H. Usando GROUPING SETS com elementos compostos

No exemplo a seguir, a lista GROUPING SETS contém dois elementos compostos, (T.[Group], T.CountryRegionCode) e (DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)). Cada elemento composto é tratado como uma coluna.

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,DATEPART(yyyy,OrderDate) AS 'Year'
    ,DATEPART(mm,OrderDate) AS 'Month'
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND DATEPART(yyyy,OrderDate) = '2004'
GROUP BY GROUPING SETS(
    (T.[Group], T.CountryRegionCode)
    ,(DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)))
ORDER BY T.[Group], T.CountryRegionCode
    ,DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate);

Conjunto de resultados.

Região	País	Ano	Mês	Total de vendas
NULL	NULL	2004	1	208553.6
NULL	NULL	2004	2	819466.6
NULL	NULL	2004	3	298579.1
NULL	NULL	2004	4	294427.7
NULL	NULL	2004	5	1070679
NULL	NULL	2004	6	339495.1
Europa	DE	NULL	NULL	1196260
Europa	FR	NULL	NULL	1834941

I. Usando GROUP BY com vários GROUPING SETS

No exemplo a seguir, a lista GROUPING SETS possui cinco elementos. O conjunto de resultados possui uma linha para os seguintes elementos:

• Cada combinação exclusiva de valores nas colunas Region e Country

• Cada valor exclusivo na coluna Store

• Cada combinação exclusiva de valores nas colunas SalesPersonID e Region

• Cada valor exclusivo na coluna SalesPersonID

• Um total geral

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY GROUPING SETS(
    (T.[Group], T.CountryRegionCode)
    ,(S.Name)
    ,(H.SalesPersonID,T.[Group])
    ,(H.SalesPersonID)
    ,())
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

Conjunto de resultados.

Região	País	Loja	ID do vendedor	Total de vendas
NULL	NULL	NULL	NULL	297597.8
NULL	NULL	NULL	284	33633.59
NULL	NULL	NULL	286	246272.4
NULL	NULL	NULL	289	17691.83
NULL	NULL	Spa e academias de ginástica	NULL	279046.8
NULL	NULL	Empresa de material esportivo Versátil	NULL	18551.07
Europa	NULL	NULL	284	33633.59
Europa	NULL	NULL	286	246272.4
Europa	NULL	NULL	289	17691.83
Europa	DE	NULL	NULL	18551.07
Europa	FR	NULL	NULL	279046.8

J. Usando GROUPING SETS com ROLLUP de parte da lista GROUP BY

No exemplo a seguir, a lista GROUPING SETS inclui agrupamentos para as colunas T.[Group] e T.CountryRegionCode e um ROLLUP das colunas S.Name e H.SalesPersonID.

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY GROUPING SETS(
    T.[Group], T.CountryRegionCode
   ,ROLLUP(S.Name, H.SalesPersonID))
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

Conjunto de resultados.

Região	País	Loja	ID do vendedor	Total de vendas
NULL	NULL	NULL	NULL	297597.8
NULL	NULL	Spa e academias de ginástica	NULL	279046.8
NULL	NULL	Spa e academias de ginástica	284	32774.36
NULL	NULL	Spa e academias de ginástica	286	246272.4
NULL	NULL	Empresa de material esportivo Versátil	NULL	18551.07
NULL	NULL	Empresa de material esportivo Versátil	284	859.232
NULL	NULL	Empresa de material esportivo Versátil	289	17691.83
NULL	DE	NULL	NULL	18551.07
NULL	FR	NULL	NULL	279046.8
Europa	NULL	NULL	NULL	297597.8

K. Usando GROUPING SETS com CUBE de parte da lista GROUP BY

No exemplo a seguir, a lista GROUPING SETS inclui agrupamentos para as colunas T.[Group] e T.CountryRegionCode e um CUBE das colunas S.Name e H.SalesPersonID.

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY GROUPING SETS(
    T.[Group], T.CountryRegionCode
    ,CUBE(S.Name, H.SalesPersonID))
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

Conjunto de resultados.

Região	País	Loja	ID do vendedor	Total de vendas
NULL	NULL	NULL	NULL	297597.8
NULL	NULL	NULL	284	33633.59
NULL	NULL	NULL	286	246272.4
NULL	NULL	NULL	289	17691.83
NULL	NULL	Spa e academias de ginástica	NULL	279046.8
NULL	NULL	Spa e academias de ginástica	284	32774.36
NULL	NULL	Spa e academias de ginástica	286	246272.4
NULL	NULL	Empresa de material esportivo Versátil	NULL	18551.07
NULL	NULL	Empresa de material esportivo Versátil	284	859.232
NULL	NULL	Empresa de material esportivo Versátil	289	17691.83
NULL	DE	NULL	NULL	18551.07
NULL	FR	NULL	NULL	279046.8
Europa	NULL	NULL	NULL	297597.8

Consulte também

Referência

GROUP BY (Transact-SQL)

GROUPING (Transact-SQL)

GROUPING_ID (Transact-SQL)

Conceitos

Equivalentes GROUPING SETS

Solucionando problemas de erros de GROUP BY