Freigeben über


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.

HinweisHinweis

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