Article
03/31/2012

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.

Notes

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

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

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

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