Share via


Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS

The ROLLUP, CUBE, and GROUPING SETS operators are extensions of the GROUP BY clause. The ROLLUP, CUBE, or GROUPING SETS operators can generate the same result set as when you use UNION ALL to combine single grouping queries; however, using one of the GROUP BY operators is usually more efficient.

The GROUPING SETS operator can generate the same result set as that generated by using a simple GROUP BY, ROLLUP, or CUBE operator. When all the groupings that are generated by using a full ROLLUP or CUBE operator are not required, you can use GROUPING SETS to specify only the groupings that you want. The GROUPING SETS list can contain duplicate groupings; and, when GROUPING SETS is used with ROLLUP and CUBE, it might generate duplicate groupings. Duplicate groupings are retained as they would be by using UNION ALL.

Note

CUBE, ROLLUP and GROUPING SETS do not support the CHECKSUM_AGG function.

Composite and Concatenated Elements

Multiple columns that are in inner parentheses in theGROUPING SETS list are treated as a single set. For example, in the clause GROUP BY GROUPING SETS ((Colum1, Column2), Column3, Column4), Column1 and Column2 are treated as one column. For an example of how to use GROUPING SETS with composite elements, see example H later in this topic.

When the GROUPING SETS list contains multiple sets in inner parentheses, separated by commas, the output of the sets are concatenated. The result set is the cross product or Cartesian product of the grouping sets. For an example of how to use GROUP BY with concatenated ROLLUP operations, see example D later in this topic.

ROLLUP and CUBE Compared to OLAP Dimensions

Queries that use the ROLLUP and CUBE operators generate some of the same result sets and perform some of the same calculations as OLAP applications. The CUBE operator generates a result set that can be used for cross tabulation reports. A ROLLUP operation can calculate the equivalent of an OLAP dimension or hierarchy.

For example, given a time dimension with the levels or attributes year, month, and day; the following ROLLUP operation generates the following groupings.

Operation

Groupings

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

year, month, day

year, month

year

()

Given a location dimension with the levels region and city concatenated with the time-dimension levels year, month, and day, the following ROLLUP operation outputs the following groupings.

Operation

Groupings

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

()

A CUBE operation of the same levels from the location and time dimensions outputs the following groupings.

Operation

Grouping

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 Result Sets

In the result sets that are generated by the GROUP BY operators, NULL has the following uses:

  • If a grouping column contains NULL, all null values are considered equal, and they are put into one NULL group.

  • When a column is aggregated in a row, the value of the column is shown as NULL.

The following example uses the GROUPING function to show the two uses of NULL. UNKNOWN replaces NULL in rows where the nulls in a column have been grouped. ALL replaces NULL in a column where NULL indicates that a column has been included in an aggregation.

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

Here is the result set.

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

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

EXAMPLES

The examples in this section use the SUM aggregate function so that the result sets can be compared. The other aggregate functions might also be used to calculate different summaries.

A. Using a simple GROUP BY

In the following example, the simple GROUP BY returns a result set to compare to the result sets of examples B through K. These examples use the GROUP BY operators with the same SELECT statement.

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;

Here is the result set.

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

In the following example, the ROLLUP operator returns a result set that contains the following groupings:

  • Region, Country, Store, and SalesPersonID

  • Region, Country, and Store

  • Region, and Country

  • Region

  • grand total

The number of groupings that is generated by ROLLUP is the same as the number of columns in the ROLLUP list plus a grand total grouping. The number of rows in a grouping is determined by the number of unique combinations of values in the columns of the grouping.

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;

Here is the result set.

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. Using GROUP BY ROLLUP with the column order reversed

In the following example, the ROLLUP operator returns a result set that contains the following groupings:

  • SalesPersonID, Store, Country, and Region

  • SalesPersonID, Store, and Country

  • SalesPersonID, and Store

  • SalesPersonID

  • grand total

The columns in the ROLLUP list are the same as those in example B, but they are in the opposite order. Columns are rolled up from right to left; therefore, the order affects the groupings. The number of rows in the result set might vary with the column order.

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

Here is the result set.

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. Using GROUP BY with concatenated ROLLUP operations

In the following example, the cross product of the two ROLLUP operations is returned.

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

Here is the result set.

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. Using GROUP BY CUBE

In the following example, the CUBE operator returns a result set that has one grouping for all possible combinations of columns in the CUBE list and a grand total grouping.

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;

Here is the result set.

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. Using CUBE with composite elements

In the following example, the CUBE operator returns a result set that has one grouping for all possible combinations of columns in the CUBE list and a grand total grouping.

The operator processes the grouped columns (T.[Group], T.CountryRegionCode) and (DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)) each as a single column.

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

Here is the result set.

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. Using GROUP BY with GROUPING SETS

In the following example, the GROUPING SETS operator has four groupings, one for each column in the SELECT list. The operator returns one row for each unique value in the Region, Country, Store, and SalesPersonID columns.

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;

Here is the result set.

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. Using GROUPING SETS with composite elements

In the following example, the GROUPING SETS list contains two composite elements, (T.[Group], T.CountryRegionCode) and (DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)). Each composite element is treated as one column.

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

Here is the result set.

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. Using GROUP BY with multiple GROUPING SETS

In the following example, the GROUPING SETS list has five elements. The result set has one row for the following elements:

  • Each unique combination of values in the Region and Country columns

  • Each unique value in the Store column

  • Each unique combination of values in the SalesPersonID and Region columns

  • Each unique value in the SalesPersonID column

  • A grand total

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;

Here is the result set.

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. Using GROUPING SETS with a ROLLUP of part of the GROUP BY list

In the following example, the GROUPING SETS list includes groupings for columns T.[Group] and T.CountryRegionCode and a ROLLUP of columns S.Name and 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;

Here is the result set.

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. Using GROUPING SETS with a CUBE of part of the GROUP BY list

In the following example, the GROUPING SETS list includes groupings for columns T.[Group] and T.CountryRegionCode and a CUBE of columns S.Name and 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;

Here is the result set.

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