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.
