Verwenden von GROUP BY mit ROLLUP, CUBE und GROUPING SETS

Die Operatoren ROLLUP, CUBE und GROUPING SETS sind Erweiterungen der GROUP BY-Klausel. Die Operatoren ROLLUP, CUBE und GROUPING SETS können das gleiche Resultset generieren wie die Verwendung von UNION ALL zum Kombinieren von einzelnen Gruppierungsabfragen. Die Verwendung eines der GROUP BY-Operatoren ist jedoch normalerweise effizienter.

Der GROUPING SETS-Operator kann das gleiche Resultset generieren wie die Verwendung eines einfachen GROUP BY-, ROLLUP- oder CUBE-Operators. Wenn alle Gruppierungen, die mithilfe eines vollständigen ROLLUP- oder CUBE-Operators generiert wurden, nicht erforderlich sind, können Sie mit GROUPING SETS nur die gewünschten Gruppierungen angeben. Die GROUPING SETS-Liste kann doppelte Gruppierungen enthalten. Wenn GROUPING SETS mit ROLLUP und CUBE verwendet wird, werden möglicherweise doppelte Gruppierungen generiert. Doppelte Gruppierungen werden wie bei der Verwendung von UNION ALL beibehalten.

Hinweis
CUBE, ROLLUP und GROUPING SETS unterstützen die CHECKSUM_AGG-Funktion nicht.

Zusammengesetzte und verkettete Elemente

Mehrere Spalten, die in inneren Klammern in derGROUPING SETS-Liste aufgeführt sind, werden als ein Satz behandelt. In der Klausel GROUP BY GROUPING SETS ((Colum1, Column2), Column3, Column4) werden z. B. Column1 und Column2 als eine Spalte betrachtet. Ein Beispiel für die Verwendung von GROUPING SETS mit zusammengesetzten Elementen finden Sie in Beispiel H weiter unten in diesem Thema.

Wenn die GROUPING SETS-Liste mehrere Sätze in inneren Klammern enthält, die durch Kommas getrennt sind, werden die Ausgaben der Sätze verkettet. Das Resultset ist das Kreuzprodukt oder das kartesische Produkt der Gruppierungssätze. Ein Beispiel für die Verwendung von GROUP BY mit verketteten ROLLUP-Vorgängen finden Sie in Beispiel D weiter unten in diesem Thema.

ROLLUP und CUBE im Vergleich mit OLAP-Dimensionen

Abfragen, die den ROLLUP-Operator und den CUBE-Operator verwenden, generieren z. T. die gleichen Resultsets und führen z. T. die gleichen Berechnungen wie OLAP-Anwendungen aus. Der CUBE-Operator generiert ein Resultset, das für Kreuztabellenberichte verwendet werden kann. Mit einem ROLLUP-Vorgang kann die Entsprechung einer OLAP-Dimension oder -Hierarchie berechnet werden.

Bei einer Zeitdimension mit den Ebenen oder Attributen year, month und day generiert der folgende ROLLUP-Vorgang die folgenden Gruppierungen.

Vorgang	Gruppierungen
ROLLUP (DATEPART(yyyy,OrderDate) ,DATEPART(mm,OrderDate) ,DATEPART(dd,OrderDate))	year, month, day year, month year ()

Bei einer Ortsdimension mit den Ebenen region und city, die mit den Zeitdimensionsebenen year, month und day verkettet ist, gibt der folgende ROLLUP-Vorgang die folgenden Gruppierungen aus.

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

Ein CUBE-Vorgang der gleichen Ebenen in der Orts- und Zeitdimension gibt die folgenden Gruppierungen aus.

Vorgang

Gruppierung

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 in Resultsets

In den Resultsets, die von den GROUP BY-Operatoren generiert werden, hat NULL die folgenden Verwendungen:

• Wenn eine Gruppierungsspalte NULL enthält, werden alle NULL-Werte als gleich betrachtet und in einer NULL-Gruppe zusammengefasst.

• Wenn eine Spalte in einer Zeile aggregiert wird, wird der Wert der Spalte als NULL angezeigt.

Im folgenden Beispiel werden die beiden Verwendungen von NULL mithilfe der GROUPING-Funktion veranschaulicht. UNKNOWN ersetzt NULL in Zeilen, in denen die NULL-Werte in einer Spalte gruppiert wurden. ALL ersetzt NULL in einer Spalte, in der NULL angibt, dass eine Spalte in eine Aggregation eingeschlossen wurde.

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

Dies ist das Resultset.

Store	SalesYear	SalesMonth	Count
Unknown	Unknown	Unknown	1
Unknown	Unknown	January	1
Unknown	Unknown	ALL	2
Unknown	2002	Unknown	1
Unknown	2002	ALL	1
Unknown	ALL	ALL	3
Active Cycling	Unknown	Unknown	1
Active Cycling	Unknown	January	2
Active Cycling	Unknown	ALL	3
Active Cycling	2002	Unknown	1
Active Cycling	2002	ALL	1
Active Cycling	2003	Unknown	1
Active Cycling	2003	Febuary	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	Febuary	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

BEISPIELE

In den Beispielen in diesem Abschnitt wird die SUM-Aggregatfunktion verwendet, damit die Resultsets verglichen werden können. Die anderen Aggregatfunktionen könnten ebenfalls verwendet werden, um andere Zusammenfassungen zu berechnen.

A. Verwenden einer einfachen GROUP BY-Klausel

Im folgenden Beispiel gibt das einfache GROUP BY ein Resultset für den Vergleich mit den Resultsets der Beispiele B bis K zurück. In diesen Beispielen werden die GROUP BY-Operatoren mit derselben SELECT-Anweisung verwendet.

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;

Dies ist das Resultset.

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. Verwenden von GROUP BY ROLLUP

Im folgenden Beispiel gibt der ROLLUP-Operator ein Resultset zurück, das die folgenden Gruppierungen enthält:

• Region, Country, Store und SalesPersonID

• Region, Country und Store

• Region und Country

• Region

• Gesamtsumme

Die Anzahl der von ROLLUP generierten Gruppierungen entspricht der Anzahl der Spalten in der ROLLUP-Liste plus einer Gruppierung für die Gesamtsumme. Die Anzahl der Zeilen in einer Gruppierung wird von der Anzahl der eindeutigen Wertkombinationen in den Spalten der Gruppierung bestimmt.

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;

Dies ist das Resultset.

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. Verwenden von GROUP BY ROLLUP mit umgekehrter Spaltenreihenfolge

Im folgenden Beispiel gibt der ROLLUP-Operator ein Resultset zurück, das die folgenden Gruppierungen enthält:

• SalesPersonID, Store, Country und Region

• SalesPersonID, Store und Country

• SalesPersonID und Store

• SalesPersonID

• Gesamtsumme

Die Spalten in der ROLLUP-Liste entsprechen denen in Beispiel B, jedoch in umgekehrter Reihenfolge. Das Rollup der Spalten wird von rechts nach links ausgeführt. Die Reihenfolge hat daher Auswirkungen auf die Gruppierungen. Die Anzahl der Zeilen im Resultset kann abhängig von der Spaltenreihenfolge anders lauten.

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

Dies ist das Resultset.

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. Verwenden von GROUP BY mit verketteten ROLLUP-Vorgängen

Im folgenden Beispiel wird das Kreuzprodukt der beiden ROLLUP-Vorgänge zurückgegeben.

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

Dies ist das Resultset.

Region	Country	Year	Month	Total Sales
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
Europe	NULL	NULL	NULL	3031201
Europe	NULL	2004	NULL	3031201
Europe	NULL	2004	1	208553.6
Europe	NULL	2004	2	819466.6
Europe	NULL	2004	3	298579.1
Europe	NULL	2004	4	294427.7
Europe	NULL	2004	5	1070679
Europe	NULL	2004	6	339495.1
Europe	DE	NULL	NULL	1196260
Europe	DE	2004	NULL	1196260
Europe	DE	2004	1	155066.2
Europe	DE	2004	2	197801.8
Europe	DE	2004	3	180977.7
Europe	DE	2004	4	222683.4
Europe	DE	2004	5	258962
Europe	DE	2004	6	180769.1
Europe	FR	NULL	NULL	1834941
Europe	FR	2004	NULL	1834941
Europe	FR	2004	1	53487.37
Europe	FR	2004	2	621664.9
Europe	FR	2004	3	117601.4
Europe	FR	2004	4	71744.28
Europe	FR	2004	5	811716.9
Europe	FR	2004	6	158726

E. Verwenden von GROUP BY CUBE

Im folgenden Beispiel gibt der CUBE-Operator ein Resultset mit einer Gruppierung für alle möglichen Kombinationen von Spalten in derCUBE-Liste und eine Gruppierung für die Gesamtsumme zurück.

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;

Dies ist das Resultset.

Region	Country	Store	SalesPersonID	Total Sales
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 and Exercise Outfitters	NULL	279046.8
NULL	NULL	Spa and Exercise Outfitters	284	32774.36
NULL	NULL	Spa and Exercise Outfitters	286	246272.4
NULL	NULL	Versatile Sporting Goods Company	NULL	18551.07
NULL	NULL	Versatile Sporting Goods Company	284	859.232
NULL	NULL	Versatile Sporting Goods Company	289	17691.83
NULL	DE	NULL	NULL	18551.07
NULL	DE	NULL	284	859.232
NULL	DE	NULL	289	17691.83
NULL	DE	Versatile Sporting Goods Company	NULL	18551.07
NULL	DE	Versatile Sporting Goods Company	284	859.232
NULL	DE	Versatile Sporting Goods Company	289	17691.83
NULL	FR	NULL	NULL	279046.8
NULL	FR	NULL	284	32774.36
NULL	FR	NULL	286	246272.4
NULL	FR	Spa and Exercise Outfitters	NULL	279046.8
NULL	FR	Spa and Exercise Outfitters	284	32774.36
NULL	FR	Spa and Exercise Outfitters	286	246272.4
Europe	NULL	NULL	NULL	297597.8
Europe	NULL	NULL	284	33633.59
Europe	NULL	NULL	286	246272.4
Europe	NULL	NULL	289	17691.83
Europe	NULL	Spa and Exercise Outfitters	NULL	279046.8
Europe	NULL	Spa and Exercise Outfitters	284	32774.36
Europe	NULL	Spa and Exercise Outfitters	286	246272.4
Europe	NULL	Versatile Sporting Goods Company	NULL	18551.07
Europe	NULL	Versatile Sporting Goods Company	284	859.232
Europe	NULL	Versatile Sporting Goods Company	289	17691.83
Europe	DE	NULL	NULL	18551.07
Europe	DE	NULL	284	859.232
Europe	DE	NULL	289	17691.83
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	NULL	284	32774.36
Europe	FR	NULL	286	246272.4
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

F. Verwenden von CUBE mit zusammengesetzten Elementen

Im folgenden Beispiel gibt der CUBE-Operator ein Resultset mit einer Gruppierung für alle möglichen Kombinationen von Spalten in der CUBE-Liste und eine Gruppierung für die Gesamtsumme zurück.

Der Operator verarbeitet die gruppierten Spalten (T.[Group], T.CountryRegionCode) und (DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)) jeweils als einzelne Spalte.

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

Dies ist das Resultset.

Region	Country	Year	Month	Total Sales
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
Europe	DE	2004	1	155066.2
Europe	DE	2004	2	197801.8
Europe	DE	2004	3	180977.7
Europe	DE	2004	4	222683.4
Europe	DE	2004	5	258962
Europe	DE	2004	6	180769.1
Europe	FR	NULL	NULL	1834941
Europe	FR	2004	1	53487.37
Europe	FR	2004	2	621664.9
Europe	FR	2004	3	117601.4
Europe	FR	2004	4	71744.28
Europe	FR	2004	5	811716.9
Europe	FR	2004	6	158726

G. Verwenden von GROUP BY mit GROUPING SETS

Im folgenden Beispiel weist der GROUPING SETS-Operator vier Gruppierungen auf, eine für jede Spalte in der SELECT-Liste. Der Operator gibt eine Zeile für jeden eindeutigen Wert in den Spalten Region, Country, Store, und SalesPersonID zurück.

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;

Dies ist das Resultset.

Region	Country	Store	SalesPersonID	Total Sales
NULL	NULL	NULL	284	33633.59
NULL	NULL	NULL	286	246272.4
NULL	NULL	NULL	289	17691.83
NULL	NULL	Spa and Exercise Outfitters	NULL	279046.8
NULL	NULL	Versatile Sporting Goods Company	NULL	18551.07
NULL	DE	NULL	NULL	18551.07
NULL	FR	NULL	NULL	279046.8
Europe	NULL	NULL	NULL	297597.8

H. Verwenden von GROUPING SETS mit zusammengesetzten Elementen

Im folgenden Beispiel enthält die GROUPING SETS-Liste die beiden zusammengesetzten Elemente (T.[Group], T.CountryRegionCode) und (DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)). Jedes zusammengesetzte Element wird als eine Spalte behandelt.

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

Dies ist das Resultset.

Region	Country	Year	Month	Total Sales
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
Europe	FR	NULL	NULL	1834941

I. Verwenden von GROUP BY mit mehreren GROUPING SETS

Im folgenden Beispiel enthält die GROUPING SETS-Liste fünf Elemente. Das Resultset weist eine Zeile für die folgenden Elemente auf:

• Jede eindeutige Kombination der Werte in den Spalten Region und Country

• Jeder eindeutige Wert in der Spalte Store

• Jede eindeutige Kombination der Werte in den Spalten SalesPersonID und Region

• Jeder eindeutige Wert in der Spalte SalesPersonID

• Eine Gesamtsumme

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;

Dies ist das Resultset.

Region	Country	Store	SalesPersonID	Total Sales
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 and Exercise Outfitters	NULL	279046.8
NULL	NULL	Versatile Sporting Goods Company	NULL	18551.07
Europe	NULL	NULL	284	33633.59
Europe	NULL	NULL	286	246272.4
Europe	NULL	NULL	289	17691.83
Europe	DE	NULL	NULL	18551.07
Europe	FR	NULL	NULL	279046.8

J. Verwenden von GROUPING SETS mit einem ROLLUP eines Teils der GROUP BY-Liste

Im folgenden Beispiel umfasst die GROUPING SETS-Liste Gruppierungen für die Spalten T.[Group] und T.CountryRegionCode und ein ROLLUP der Spalten S.Name und 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;

Dies ist das Resultset.

Region	Country	Store	SalesPersonID	Total Sales
NULL	NULL	NULL	NULL	297597.8
NULL	NULL	Spa and Exercise Outfitters	NULL	279046.8
NULL	NULL	Spa and Exercise Outfitters	284	32774.36
NULL	NULL	Spa and Exercise Outfitters	286	246272.4
NULL	NULL	Versatile Sporting Goods Company	NULL	18551.07
NULL	NULL	Versatile Sporting Goods Company	284	859.232
NULL	NULL	Versatile Sporting Goods Company	289	17691.83
NULL	DE	NULL	NULL	18551.07
NULL	FR	NULL	NULL	279046.8
Europe	NULL	NULL	NULL	297597.8

K. Verwenden von GROUPING SETS mit einem CUBE eines Teils der GROUP BY-Liste

Im folgenden Beispiel umfasst die GROUPING SETS-Liste Gruppierungen für die Spalten T.[Group] und T.CountryRegionCode und ein CUBE der Spalten S.Name und 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;

Dies ist das Resultset.

Region	Country	Store	SalesPersonID	Total Sales
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 and Exercise Outfitters	NULL	279046.8
NULL	NULL	Spa and Exercise Outfitters	284	32774.36
NULL	NULL	Spa and Exercise Outfitters	286	246272.4
NULL	NULL	Versatile Sporting Goods Company	NULL	18551.07
NULL	NULL	Versatile Sporting Goods Company	284	859.232
NULL	NULL	Versatile Sporting Goods Company	289	17691.83
NULL	DE	NULL	NULL	18551.07
NULL	FR	NULL	NULL	279046.8
Europe	NULL	NULL	NULL	297597.8

Siehe auch

Verweis

GROUP BY (Transact-SQL)

GROUPING (Transact-SQL)

GROUPING_ID (Transact-SQL)

Konzepte

GROUPING SETS-Entsprechungen

Problembehandlung von GROUP BY-Fehlern