مقالة
10/01/2010

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

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

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

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

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

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

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

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

Here is the result set.

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

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

Here is the result set.

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

Here is the result set.

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

Here is the result set.

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

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

Here is the result set.

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

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

مشاركة عبر

Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS

Composite and Concatenated Elements

ROLLUP and CUBE Compared to OLAP Dimensions

NULL in Result Sets

EXAMPLES

A. Using a simple GROUP BY

B. Using GROUP BY ROLLUP

C. Using GROUP BY ROLLUP with the column order reversed

D. Using GROUP BY with concatenated ROLLUP operations

E. Using GROUP BY CUBE

F. Using CUBE with composite elements

G. Using GROUP BY with GROUPING SETS

H. Using GROUPING SETS with composite elements

I. Using GROUP BY with multiple GROUPING SETS

J. Using GROUPING SETS with a ROLLUP of part of the GROUP BY list

K. Using GROUPING SETS with a CUBE of part of the GROUP BY list

See Also

Reference

Concepts

الموارد الإضافية