Utilisation de GROUP BY avec ROLLUP, CUBE et GROUPING SETS

Les opérateurs ROLLUP, CUBE et GROUPING SETS sont des extensions de la clause GROUP BY. Ils peuvent générer le même jeu de résultats que lorsque vous utilisez UNION ALL pour combiner des requêtes de regroupement uniques ; toutefois, l'utilisation de l'un des opérateurs GROUP BY est habituellement plus efficace.

L'opérateur GROUPING SETS peut générer le même jeu de résultats que celui généré à l'aide d'un opérateur GROUP BY, ROLLUP ou CUBE simple. Lorsque tous les regroupements générés à l'aide d'un opérateur ROLLUP ou CUBE complet ne sont pas nécessaires, vous pouvez utiliser GROUPING SETS pour spécifier uniquement les regroupements souhaités. La liste GROUPING SETS peut contenir des regroupements en double et, lorsque GROUPING SETS est utilisé avec ROLLUP et CUBE, elle peut générer des regroupements en double. Les regroupements en double sont conservés comme ils le seraient en cas d'utilisation de UNION ALL.

Note

CUBE, ROLLUP et GROUPING SETS ne prennent pas en charge la fonction CHECKSUM_AGG.

Éléments composites et concaténés

Les colonnes multiples placées entre des parenthèses internes dans la listeGROUPING SETS sont traitées comme un jeu unique. Par exemple, dans la clause GROUP BY GROUPING SETS ((Colum1, Column2), Column3, Column4), Column1 et Column2 sont traitées comme une seule colonne. Pour obtenir un exemple d'utilisation de GROUPING SETS avec des éléments composites, consultez l'exemple H plus loin dans cette rubrique.

Lorsque la liste GROUPING SETS contient plusieurs jeux dans des parenthèses internes, séparés par des virgules, la sortie des jeux est concaténée. Le jeu de résultats est le produit croisé ou le produit cartésien des jeux de regroupement. Pour obtenir un exemple d'utilisation de GROUP BY avec des opérations ROLLUP concaténées, consultez l'exemple D plus loin dans cette rubrique.

ROLLUP et CUBE comparés aux dimensions OLAP

Les requêtes qui utilisent les opérateurs ROLLUP et CUBE génèrent certains des mêmes jeux de résultats et effectuent certains des mêmes calculs que les applications OLAP. L'opérateur CUBE génère un jeu de résultats qui peut être utilisé pour les rapports de tabulation croisés. Une opération ROLLUP peut calculer l'équivalent d'une dimension ou hiérarchie OLAP.

Par exemple, soit une dimension de temps avec les niveaux ou attributs year, month et day ; l'opération ROLLUP suivante génère les regroupements suivants.

Opération

Regroupements

ROLLUP (DATEPART(yyyy,OrderDate)
    ,DATEPART(mm,OrderDate)
    ,DATEPART(dd,OrderDate)) 

year, month, day

year, month

year

()

Soit une dimension d'emplacement avec les niveaux region et city concaténée avec les niveaux de dimension de temps year, month et day. L'opération ROLLUP suivante donne les regroupements suivants.

Opération

Regroupements

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

()

Une opération CUBE des mêmes niveaux à partir des sorties de dimensions emplacement et temps donne les regroupements suivants.

Opération

Regroupement

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 dans les jeux de résultats

Dans les jeux de résultats générés par les opérateurs GROUP BY, NULL a les utilisations suivantes :

  • Si une colonne de regroupement contient NULL, toutes les valeurs NULL sont considérées comme égales et sont placées dans un groupe NULL.

  • Lorsqu'une colonne est agrégée dans une ligne, la valeur de la colonne est affichée comme NULL.

L'exemple suivant utilise la fonction GROUPING pour illustrer les deux utilisations de NULL. UNKNOWN remplace NULL dans les lignes où les valeurs NULL dans une colonne ont été groupées. ALL remplace NULL dans une colonne où NULL indique qu'une colonne a été incluse dans une agrégation.

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);

Voici l'ensemble des résultats.

Store

SalesYear

SalesMonth

Count

Inconnu

Inconnu

Inconnu

1

Unknown

Inconnu

January

1

Inconnu

Inconnu

ALL

2

Inconnu

2002

Inconnu

1

Inconnu

2002

ALL

1

Inconnu

ALL

ALL

3

Active Cycling

Inconnu

Inconnu

1

Active Cycling

Unknown

January

2

Active Cycling

Unknown

ALL

3

Active Cycling

2002

Inconnu

1

Active Cycling

2002

ALL

1

Active Cycling

2003

Inconnu

1

Active Cycling

2003

February

1

Active Cycling

2003

ALL

2

Active Cycling

ALL

ALL

6

Mountain Bike Store

Unknown

Unknown

1

Mountain Bike Store

Unknown

ALL

1

Mountain Bike Store

2002

Unknown

1

Mountain Bike Store

2002

January

1

Mountain Bike Store

2002

ALL

2

Mountain Bike Store

2003

February

1

Mountain Bike Store

2003

January

1

Mountain Bike Store

2003

March

1

Mountain Bike Store

2003

ALL

3

Mountain Bike Store

ALL

ALL

6

ALL

ALL

ALL

15

EXEMPLES

Les exemples de cette section utilisent la fonction d'agrégation SUM afin de pouvoir comparer les jeux de résultats. Les autres fonctions d'agrégation peuvent également être utilisées pour calculer des résumés différents.

A. Utilisation d'une clause GROUP BY simple

Dans l'exemple suivant, la clause GROUP BY simple retourne un jeu de résultats afin de comparer les jeux de résultats des exemples B à K. que Ces exemples utilisent les opérateurs GROUP BY avec la même instruction SELECT.

USE AdventureWorks2008R2;
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.StoreID  = S.BusinessEntityID
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 290, 288)
    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;

Voici l'ensemble des résultats.

Region

Country

Store

SalesPersonID

Total Sales

Europe

DE

Versatile Sporting Goods Company

284

859.232

Europe

DE

Versatile Sporting Goods Company

289

17691.83

Europe

FR

Spa and Exercise Outfitters

284

32774.36

Europe

FR

Spa and Exercise Outfitters

286

246272.4

B. Utilisation de GROUP BY ROLLUP

Dans l'exemple suivant, l'opérateur ROLLUP retourne un ensemble de résultats qui contient les groupements suivants :

  • Region, Country, Store et SalesPersonID

  • Region, Country et Store

  • Regionet Country

  • Region

  • total global

Le nombre de regroupements générés par ROLLUP est identique au nombre de colonnes dans la liste ROLLUP plus un regroupement de total global. Le nombre de lignes dans un regroupement est déterminé par le nombre de combinaisons uniques de valeurs dans les colonnes du regroupement.

USE AdventureWorks2008R2;
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.StoreID  = S.BusinessEntityID
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 290, 288)
    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;

Voici l'ensemble des résultats.

Region

Country

Store

SalesPersonID

Total Sales

NULL

NULL

NULL

NULL

297597.8

Europe

NULL

NULL

NULL

297597.8

Europe

DE

NULL

NULL

18551.07

Europe

DE

Versatile Sporting Goods Company

NULL

18551.07

Europe

DE

Versatile Sporting Goods Company

284

859.232

Europe

DE

Versatile Sporting Goods Company

289

17691.83

Europe

FR

NULL

NULL

279046.8

Europe

FR

Spa and Exercise Outfitters

NULL

279046.8

Europe

FR

Spa and Exercise Outfitters

284

32774.36

Europe

FR

Spa and Exercise Outfitters

286

246272.4

C. Utilisation de GROUP BY ROLLUP avec ordre des colonnes inversé

Dans l'exemple suivant, l'opérateur ROLLUP retourne un ensemble de résultats qui contient les regroupements suivants :

  • SalesPersonID, Store, Country et Region

  • SalesPersonID, Store et Country

  • SalesPersonIDet Store

  • SalesPersonID

  • total global

Les colonnes dans la liste ROLLUP sont identiques à celles de l'exemple B, mais dans l'ordre opposé. Les colonnes sont classées de droite à gauche ; par conséquent, l'ordre affecte les regroupements. Le nombre de lignes dans le jeu de résultats peut varier selon l'ordre des colonnes.

USE AdventureWorks2008R2;
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.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 290, 288)
    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];

Voici l'ensemble des résultats.

Region

Country

Store

SalesPersonID

Total Sales

NULL

NULL

NULL

NULL

297597.8

NULL

NULL

NULL

284

33633.59

NULL

NULL

Spa and Exercise Outfitters

284

32774.36

NULL

FR

Spa and Exercise Outfitters

284

32774.36

Europe

FR

Spa and Exercise Outfitters

284

32774.36

NULL

NULL

Versatile Sporting Goods Company

284

859.232

NULL

DE

Versatile Sporting Goods Company

284

859.232

Europe

DE

Versatile Sporting Goods Company

284

859.232

NULL

NULL

NULL

286

246272.4

NULL

NULL

Spa and Exercise Outfitters

286

246272.4

NULL

FR

Spa and Exercise Outfitters

286

246272.4

Europe

FR

Spa and Exercise Outfitters

286

246272.4

NULL

NULL

NULL

289

17691.83

NULL

NULL

Versatile Sporting Goods Company

289

17691.83

NULL

DE

Versatile Sporting Goods Company

289

17691.83

Europe

DE

Versatile Sporting Goods Company

289

17691.83

D. Utilisation de GROUP BY avec des opérations ROLLUP concaténées

Dans l'exemple suivant, le produit croisé des deux opérations ROLLUP est retourné.

USE AdventureWorks2008R2;
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.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND DATEPART(yyyy,OrderDate) = '2006'
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);

Voici l'ensemble des résultats.

Region

Country

Year

Month

Total Sales

NULL

NULL

NULL

NULL

966221.9606

NULL

NULL

2006

NULL

966221.9606

NULL

NULL

2006

7

109936.0248

NULL

NULL

2006

8

296651.4808

NULL

NULL

2006

9

184477.7563

NULL

NULL

2006

10

62792.5455

NULL

NULL

2006

11

213238.0125

NULL

NULL

2006

12

99126.1407

Europe

NULL

NULL

NULL

966221.9606

Europe

NULL

2006

NULL

966221.9606

Europe

NULL

2006

7

109936.0248

Europe

NULL

2006

8

296651.4808

Europe

NULL

2006

9

184477.7563

Europe

NULL

2006

10

62792.5455

Europe

NULL

2006

11

213238.0125

Europe

NULL

2006

12

99126.1407

Europe

FR

NULL

NULL

966221.9606

Europe

FR

2006

NULL

966221.9606

Europe

FR

2006

7

109936.0248

Europe

FR

2006

8

296651.4808

Europe

FR

2006

9

184477.7563

Europe

FR

2006

10

62792.5455

Europe

FR

2006

11

213238.0125

Europe

FR

2006

12

99126.1407

E. Utilisation de GROUP BY CUBE

Dans l'exemple suivant, l'opérateur CUBE retourne un jeu de résultats qui a un regroupement pour toutes les combinaisons possibles de colonnes dans la liste CUBE et un regroupement de total global.

USE AdventureWorks2008R2;
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 AS C
    INNER JOIN Sales.Store AS S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory AS T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader AS H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 288, 290)
    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;

Voici l'ensemble des résultats.

Region

Country

Store

SalesPersonID

Total Sales

NULL

NULL

NULL

NULL

254013.6014

NULL

NULL

NULL

287

28461.1854

NULL

NULL

NULL

288

17073.0655

NULL

NULL

NULL

290

208479.3505

NULL

NULL

Spa and Exercise Outfitters

NULL

236210.9015

NULL

NULL

Spa and Exercise Outfitters

287

27731.551

NULL

NULL

Spa and Exercise Outfitters

290

208479.3505

NULL

NULL

Versatile Sporting Goods Company

NULL

17802.6999

NULL

NULL

Versatile Sporting Goods Company

287

729.6344

NULL

NULL

Versatile Sporting Goods Company

288

17073.0655

NULL

DE

NULL

NULL

17802.6999

NULL

DE

NULL

287

729.6344

NULL

DE

NULL

288

17073.0655

NULL

DE

Versatile Sporting Goods Company

NULL

17802.6999

NULL

DE

Versatile Sporting Goods Company

287

729.6344

NULL

DE

Versatile Sporting Goods Company

288

17073.0655

NULL

FR

NULL

NULL

236210.9015

NULL

FR

NULL

287

27731.551

NULL

FR

NULL

290

208479.3505

NULL

FR

Spa and Exercise Outfitters

NULL

236210.9015

NULL

FR

Spa and Exercise Outfitters

287

27731.551

NULL

FR

Spa and Exercise Outfitters

290

208479.3505

Europe

NULL

NULL

NULL

254013.6014

Europe

NULL

NULL

287

28461.1854

Europe

NULL

NULL

288

17073.0655

Europe

NULL

NULL

290

208479.3505

Europe

NULL

Spa and Exercise Outfitters

NULL

236210.9015

Europe

NULL

Spa and Exercise Outfitters

287

27731.551

Europe

NULL

Spa and Exercise Outfitters

290

208479.3505

Europe

NULL

Versatile Sporting Goods Company

NULL

17802.6999

Europe

NULL

Versatile Sporting Goods Company

287

729.6344

Europe

NULL

Versatile Sporting Goods Company

288

17073.0655

Europe

DE

NULL

NULL

17802.6999

Europe

DE

NULL

287

729.6344

Europe

DE

NULL

288

17073.0655

Europe

DE

Versatile Sporting Goods Company

NULL

17802.6999

Europe

DE

Versatile Sporting Goods Company

287

729.6344

Europe

DE

Versatile Sporting Goods Company

288

17073.0655

Europe

FR

NULL

NULL

236210.9015

Europe

FR

NULL

287

27731.551

Europe

FR

NULL

290

208479.3505

Europe

FR

Spa and Exercise Outfitters

NULL

236210.9015

Europe

FR

Spa and Exercise Outfitters

287

27731.551

Europe

FR

Spa and Exercise Outfitters

290

208479.3505

F. Utilisation de CUBE avec des éléments composites

Dans l'exemple suivant, l'opérateur CUBE retourne un jeu de résultats qui a un regroupement pour toutes les combinaisons possibles de colonnes dans la liste CUBE et un regroupement de total global.

L'opérateur traite chacune des colonnes groupées (T.[Group], T.CountryRegionCode) et (DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)) comme une colonne unique.

USE AdventureWorks2008R2;
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.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND DATEPART(yyyy,OrderDate) = '2006'
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);

Voici l'ensemble des résultats.

Region

Country

Year

Month

Total Sales

NULL

NULL

NULL

NULL

966221.9606

NULL

NULL

2006

7

109936.0248

NULL

NULL

2006

8

296651.4808

NULL

NULL

2006

9

184477.7563

NULL

NULL

2006

10

62792.5455

NULL

NULL

2006

11

213238.0125

NULL

NULL

2006

12

99126.1407

Europe

FR

NULL

NULL

966221.9606

Europe

FR

2006

7

109936.0248

Europe

FR

2006

8

296651.4808

Europe

FR

2006

9

184477.7563

Europe

FR

2006

10

62792.5455

Europe

FR

2006

11

213238.0125

Europe

FR

2006

12

99126.1407

G. Utilisation de GROUP BY avec GROUPING SETS

Dans l'exemple suivant, l'opérateur GROUPING SETS a quatre regroupements, un pour chaque colonne dans la liste SELECT. L'opérateur retourne une ligne pour chaque valeur unique dans les colonnes Region, Country, Store, et SalesPersonID .

USE AdventureWorks2008R2;
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 AS S
        ON C.StoreID  = S.BusinessEntityId 
    INNER JOIN Sales.SalesTerritory AS T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader AS H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 288, 290)
    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;

Voici l'ensemble des résultats.

Region

Country

Store

SalesPersonID

Total Sales

NULL

NULL

NULL

287

28461.1854

NULL

NULL

NULL

288

17073.0655

NULL

NULL

NULL

290

208479.3505

NULL

NULL

Spa and Exercise Outfitters

NULL

236210.9015

NULL

NULL

Versatile Sporting Goods Company

NULL

17802.6999

NULL

DE

NULL

NULL

17802.6999

NULL

FR

NULL

NULL

236210.9015

Europe

NULL

NULL

NULL

254013.6014

H. Utilisation de GROUPING SETS avec des éléments composites

Dans l'exemple suivant, la liste GROUPING SETS contient deux éléments composites, (T.[Group], T.CountryRegionCode) et (DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)). Chaque élément composite est traité comme une colonne.

USE AdventureWorks2008R2;
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 AS S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory AS T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader AS H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND DATEPART(yyyy,OrderDate) = '2006'
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);

Voici l'ensemble des résultats.

Region

Country

Year

Month

Total Sales

NULL

NULL

2006

7

109936.0248

NULL

NULL

2006

8

296651.4808

NULL

NULL

2006

9

184477.7563

NULL

NULL

2006

10

62792.5455

NULL

NULL

2006

11

213238.0125

NULL

NULL

2006

12

99126.1407

Europe

FR

NULL

NULL

966221.9606

I. Utilisation de GROUP BY avec plusieurs GROUPING SETS

Dans l'exemple suivant, la liste GROUPING SETS a cinq éléments. Le jeu de résultats a une ligne pour les éléments suivants :

  • Chaque combinaison unique de valeurs dans les colonnes Region et Country

  • Chaque valeur unique dans la colonne Store

  • Chaque combinaison unique de valeurs dans les colonnes SalesPersonID et Region

  • Chaque valeur unique dans la colonne SalesPersonID

  • Un total global

USE AdventureWorks2008R2;
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 AS S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory AS T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader AS H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 288, 290)
    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;

Voici l'ensemble des résultats.

Region

Country

Store

SalesPersonID

Total Sales

NULL

NULL

NULL

NULL

254013.6014

NULL

NULL

NULL

287

28461.1854

NULL

NULL

NULL

288

17073.0655

NULL

NULL

NULL

290

208479.3505

NULL

NULL

Spa and Exercise Outfitters

NULL

236210.9015

NULL

NULL

Versatile Sporting Goods Company

NULL

17802.6999

Europe

NULL

NULL

287

28461.1854

Europe

NULL

NULL

288

17073.0655

Europe

NULL

NULL

290

208479.3505

Europe

DE

NULL

NULL

17802.6999

Europe

FR

NULL

NULL

236210.9015

J. Utilisation de GROUPING SETS avec un ROLLUP d'une partie de la liste GROUP BY

Dans l'exemple suivant, la liste GROUPING SETS inclut des regroupements pour les colonnes T.[Group] et T.CountryRegionCode et un ROLLUP des colonnes S.Name et H.SalesPersonID.

USE AdventureWorks2008R2;
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 AS S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory AS T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader AS H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 288, 290)
    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;

Voici l'ensemble des résultats.

Region

Country

Store

SalesPersonID

Total Sales

NULL

NULL

NULL

NULL

254013.6014

NULL

NULL

Spa and Exercise Outfitters

NULL

236210.9015

NULL

NULL

Spa and Exercise Outfitters

287

27731.551

NULL

NULL

Spa and Exercise Outfitters

290

208479.3505

NULL

NULL

Versatile Sporting Goods Company

NULL

17802.6999

NULL

NULL

Versatile Sporting Goods Company

287

729.6344

NULL

NULL

Versatile Sporting Goods Company

288

17073.0655

NULL

DE

NULL

NULL

17802.6999

NULL

FR

NULL

NULL

236210.9015

Europe

NULL

NULL

NULL

254013.6014

K. Utilisation de GROUPING SETS avec un CUBE d'une partie de la liste GROUP BY

Dans l'exemple suivant, la liste GROUPING SETS inclut des regroupements pour les colonnes T.[Group] et T.CountryRegionCode et un CUBE des colonnes S.Name et H.SalesPersonID.

USE AdventureWorks2008R2;
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 AS S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory AS T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader AS H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 288, 290)
    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;

Voici l'ensemble des résultats.

Region

Country

Store

SalesPersonID

Total Sales

NULL

NULL

NULL

NULL

254013.6014

NULL

NULL

NULL

287

28461.1854

NULL

NULL

NULL

288

17073.0655

NULL

NULL

NULL

290

208479.3505

NULL

NULL

Spa and Exercise Outfitters

NULL

236210.9015

NULL

NULL

Spa and Exercise Outfitters

287

27731.551

NULL

NULL

Spa and Exercise Outfitters

290

208479.3505

NULL

NULL

Versatile Sporting Goods Company

NULL

17802.6999

NULL

NULL

Versatile Sporting Goods Company

287

729.6344

NULL

NULL

Versatile Sporting Goods Company

288

17073.0655

NULL

DE

NULL

NULL

17802.6999

NULL

FR

NULL

NULL

236210.9015

Europe

NULL

NULL

NULL

254013.6014