T-SQL Programming Part 12 – Using the ROLLUP, CUBE, and GROUPING SETS Operators

In my last article I discussed the GROUP BY clause.  This article will expand on the GROUP BY topic by discussing the ROLLUP, CUBE and GROUPING SETS operators.  These operators are used with the GROUP BY clause and allow you to create subtotals, grand totals and superset of subtotals.  Read on to find out more about these additional GROUP BY operators.

Understanding the ROLLUP, CUBE and GROUPING SETS Operators

What do the ROLLUP, CUBE, and GROUPING SETS operators do?  They allow you to create subtotals and grand totals a number of different ways. 

The ROLLUP operator is used with the GROUP BY clause.  It is used to create subtotals and grand totals for a set of columns.  The summarized amounts are created based on the columns passed to the ROLLUP operator.

The CUBE operators, like the ROLLUP operator produces subtotals and grand totals as well.  But unlike the ROLLUP operator it produces subtotals and grand totals for every permutation of the columns provided to the CUBE operator.

Lastly the GROUPING SETS operator allows you to group your data a number of different ways in a single SELECT statement.  To better understand these three different grouping operators let’s review some examples of how they can be used.

Sample Data for Examples

Before I can show you how to use these different GROUP BY operators I first need to generate some test data that can be used for my examples.  The code below will create a sample table that I will be using for all of my examples.

SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE PurchaseItem (
      PurchaseID smallint identity, 
      Supplier varchar(50),
      PurchaseType varchar(20), 
      PurchaseAmt money, 
      PurchaseDate date);
INSERT INTO PurchaseItem VALUES
    ('McLendon''s','Hardware',2121.09,'2014-01-12'),
      ('Bond','Electrical',12347.87,'2014-01-18'),
      ('Craftsman','Hardware',999.99,'2014-01-22'),
      ('Stanley','Hardware',6532.09,'2014-01-31'),
      ('RubberMaid','Kitchenware',3421.10,'2014-02-03'),
      ('RubberMaid','KitchenWare',1290.90,'2014-02-07'),
      ('Glidden','Paint',12987.01,'2014-02-10'),
      ('Dunn''s','Lumber',43235.67,'2014-02-21'),
      ('Maytag','Appliances',89320.19,'2014-03-10'),
      ('Amana','Appliances',53821.19,'2014-03-12'),
      ('Lumber Surplus','Lumber',3245.59,'2014-03-14'),
      ('Global Source','Outdoor',3331.59,'2014-03-19'),
      ('Scott''s','Garden',2321.01,'2014-03-21'),
      ('Platt','Electrical',3456.01,'2014-04-03'),
      ('Platt','Electrical',1253.87,'2014-04-21'),
      ('RubberMaid','Kitchenware',3332.89,'2014-04-20'),
      ('Cresent','Lighting',345.11,'2014-04-22'),
      ('Snap-on','Hardware',2347.09,'2014-05-03'),
      ('Dunn''s','Lumber',1243.78,'2014-05-08'),
      ('Maytag','Appliances',89876.90,'2014-05-10'),
      ('Parker','Paint',1231.22,'2014-05-10'),
      ('Scotts''s','Garden',3246.98,'2014-05-12'),
      ('Jasper','Outdoor',2325.98,'2014-05-14'),
      ('Global Source','Outdoor',8786.99,'2014-05-21'),
      ('Craftsman','Hardware',12341.09,'2014-05-22');
     
     

You can create this table if you want to follow along with my examples.

ROLLUP Examples

The ROLLUP operator allows SQL Server to create subtotals and grand totals, while it groups data using the GROUP BY clause.  For my first example let me use the ROLLUP operator to generator a grand total by PurchaseType by running this code:

USE tempdb;
GO
SELECT coalesce (PurchaseType,'GrandTotal') AS PurchaseType
     , Sum(PurchaseAmt) as SummorizedPurchaseAmt
FROM PurchaseItem
GROUP BY ROLLUP(PurchaseType);

When I run this code I get this output:

PurchaseType         SummorizedPurchaseAmt
-------------------- ---------------------
Appliances           233018.28
Electrical           17057.75
Garden               5567.99
Hardware             24341.35
Kitchenware          8044.89
Lighting             345.11
Lumber               47725.04
Outdoor              14444.56
Paint                14218.23
GrandTotal           364763.20

By reviewing the output above you can see that this code created subtotals for all the different PurchaseTypes and then at the end produced a GrandTotal for all the PurchaseTypes combined.   If you look at the code above, I got the PurchaseType of “Grand Total” to display by using the coalesce clause.  Without the coalesce clause the PurchaceType column value would have been “Null’ for the grand total row.

Suppose I wanted to calculate the subtotals of ProductTypes by month, with a monthly total amount for all the products sold in the month.  I could do that by running the following code:

USE tempdb;
GO
SELECT month(PurchaseDate) PurchaseMonth
     , CASE WHEN month(PurchaseDate) is null then 'Grand Total' 
                   ELSE coalesce (PurchaseType,'Monthly Total') end AS PurchaseType
     , Sum(PurchaseAmt) as SummorizedPurchaseAmt
FROM PurchaseItem
GROUP BY ROLLUP(month(PurchaseDate), PurchaseType);
 
When I run this code I get this output:
 
PurchaseMonth PurchaseType         SummorizedPurchaseAmt
------------- -------------------- ---------------------
1             Electrical           12347.87
1             Hardware             9653.17
1             Monthly Total        22001.04
2             Kitchenware          4712.00
2             Lumber               43235.67
2             Paint                12987.01
2             Monthly Total        60934.68
3             Appliances           143141.38
3             Garden               2321.01
3             Lumber               3245.59
3             Outdoor              3331.59
3             Monthly Total        152039.57
4             Electrical           4709.88
4             Kitchenware          3332.89
4             Lighting             345.11
4             Monthly Total        8387.88
5             Appliances           89876.90
5             Garden               3246.98
5             Hardware             14688.18
5             Lumber               1243.78
5             Outdoor              11112.97
5             Paint                1231.22
5             Monthly Total        121400.03
NULL          Grand Total          364763.20
 
 

Here I have included two columns in the ROLLUP clause.  The first column was the month of the purchase, and the second column is PurchaseType.  This allowed me to create the subtotals by ProductType by month, as well as Monthly Total amount at the end of every month.  Additionally this code creates a Grant Total amount of all product sales at the end.

CUBE Example

The CUBE operator allows you to summarize your data similar to the ROLLUP operator.  The only difference is the CUBE operator will summarize your data based on every permutation of the columns passed to the CUBE operator.  To demonstrate this I will run the code below:

USE tempdb;
GO
SELECT month(PurchaseDate) PurchaseMonth
     , CASE WHEN month(PurchaseDate) is null 
                  THEN coalesce ('Grand Total for ' + PurchaseType,'Grand Total')  
                ELSE coalesce (PurchaseType,'Monthly SubTotal') end AS PurchaseType
     , Sum(PurchaseAmt) as SummorizedPurchaseAmt
FROM PurchaseItem
GROUP BY CUBE(month(PurchaseDate), PurchaseType);
 

When I run this code it will generates summarized amounts for every permutation of the columns passed to the CUBE operator.  In my example I had two values pass to the CUBE operator: “month(PurchaseDate)” and “PurchaseType”.  Therefore the different permutations of summarized amounts are “PurchaseType”, “PurchaseType and month(PurchaseDate)”, and then lastly “month(PurchaseDate)”.   You can see these different summarized amounts in the output below:

PurchaseMonth PurchaseType                         SummorizedPurchaseAmt
------------- ------------------------------------ ---------------------
3             Appliances                           143141.38
5             Appliances                           89876.90
NULL          Grand Total for Appliances           233018.28
1             Electrical                           12347.87
4             Electrical                           4709.88
NULL          Grand Total for Electrical           17057.75
3             Garden                               2321.01
5             Garden                               3246.98
NULL          Grand Total for Garden               5567.99
1             Hardware                             9653.17
5             Hardware                             14688.18
NULL          Grand Total for Hardware             24341.35
2             Kitchenware                          4712.00
4             Kitchenware                          3332.89
NULL          Grand Total for Kitchenware          8044.89
4             Lighting                             345.11
NULL          Grand Total for Lighting             345.11
2             Lumber                               43235.67
3             Lumber                               3245.59
5             Lumber                               1243.78
NULL          Grand Total for Lumber               47725.04
3             Outdoor                              3331.59
5             Outdoor                              11112.97
NULL          Grand Total for Outdoor              14444.56
2             Paint                                12987.01
5             Paint                                1231.22
NULL          Grand Total for Paint                14218.23
NULL          Grand Total                          364763.20
1             Monthly SubTotal                     22001.04
2             Monthly SubTotal                     60934.68
3             Monthly SubTotal                     152039.57
4             Monthly SubTotal                     8387.88
5             Monthly SubTotal                     121400.03
 

The results above first generated the subtotals for each PurchaseType by month, followed by the Grand Total for each PurchaseType.  Once each PurchaseType is displayed by month with their Grand Total amounts this code then produces a “Grand Total” amount for all purchases.   Lastly it produces the monthly subtotals.   

GROUPING SETS Example

Sometimes you want to group your data multiple different ways.  The GROUPING SETS operator allows you to do this with a single SELECT statement, instead of multiple SELECT statements with different GROUP BY clauses union-ed together.  To demonstrate, review the code in below:

USE tempdb;
GO
SELECT month(PurchaseDate) PurchaseMonth
     , PurchaseType AS PurchaseType
     , Sum(PurchaseAmt) as SummorizedPurchaseAmt
FROM PurchaseItem
GROUP BY GROUPING SETS (month(PurchaseDate), PurchaseType);
 

When run the code above I get the following output:

PurchaseMonth PurchaseType         SummorizedPurchaseAmt
------------- -------------------- ---------------------
NULL          Appliances           233018.28
NULL          Electrical           17057.75
NULL          Garden               5567.99
NULL          Hardware             24341.35
NULL          Kitchenware          8044.89
NULL          Lighting             345.11
NULL          Lumber               47725.04
NULL          Outdoor              14444.56
NULL          Paint                14218.23
1             NULL                 22001.04
2             NULL                 60934.68
3             NULL                 152039.57
4             NULL                 8387.88
5             NULL                 121400.03
 
 

Here you can see SQL Server first groups my sample data based on the PurchaseType, then it groups the data based on purchase month.  This code is equivalent to the code below that uses two SELECT statements with the output of both statement joined using a UNION ALL clause:

USE tempdb;
GO
SELECT NULL as PurchaseMonth
     , PurchaseType
     , Sum(PurchaseAmt) as SummorizedPurchaseAmt
FROM PurchaseItem
GROUP BY PurchaseType
UNION ALL
SELECT month(PurchaseDate) AS PurchaseMonth
     , NULL as PurchaseType
     , Sum(PurchaseAmt) as SummorizedPurchaseAmt
FROM PurchaseItem
GROUP BY month(PurchaseDate)

If you run the code above you will see that that it produces the same results as the single SELECT statement with a GROUPING SETS clause. 

Summary

As you can see using the ROLLUP, CUBE or GROUPING SETS clauses allow you many different ways to produce subtotal and grand total values for a set of records.  Having these different methods to create subtotals and grand totals allows you more options for how you can summarize your data with a single SELECT statement.  The next time you want to produce multiple summarized record sets try using ROLLUP, CUBE, or GROUPING SETS to obtain your summarized data. 

See all articles by Greg Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles