Compartilhar via


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çãoObservaçã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