Most SQL developers have probably used GROUP BY to create summaries of their data, to count records or to sum up amounts. SQL server gives us a few additional options to summarize data: CUBE, ROLLUP and GROUPING SETS. I’ve put together this port to demo these features.
I’ve posted the complete SQL script on GitHub

Sample Data:

Country StateProvince City SalesCount
Canada British Columbia Vancouver 3
Canada Ontario Toronto 5
Canada Ontario London 2
US Georgia Atlanta 10
US Georgia Columbus 1
US Texas Dallas 8
US Texas Houston 11

Grouping Sets:

SELECT Country, StateProvince, SUM(SalesCount) as TotalSalesCount
FROM dbo.Sales
GROUP BY GROUPING SETS (
	(Country), (Country, StateProvince), ()
)
ORDER BY Country, StateProvince;

Running this query gives these results:

Country StateProvince SalesCount
NULL NULL 40
Canada NULL 10
Canada British Columbia 3
Canada Ontario 7
US NULL 30
US Georgia 11
US Texas 19

With the Grouping Sets, we’ve specified three different groups: The empty set, by Country, and by Country and State/Province. We can see in the results that we get the Sales Count for these three different groupings. We get a grand total of all the sales, a summary of sales per country, and then a summary of sales for each Country and State/Province grouping.

Cube:

SELECT Country, StateProvince, SUM(SalesCount) as TotalSalesCount 
FROM dbo.Sales
GROUP BY CUBE(Country, StateProvince)
ORDER BY Country, StateProvince;

Running the CUBE query gives us these results:

Country StateProvince SalesCount
NULL NULL 40
NULL British Columbia 3
NULL Georgia 11
NULL Ontario 7
NULL Texas 19
Canada NULL 10
Canada British Columbia 3
Canada Ontario 7
US NULL 30
US Georgia 11
US Texas 19

CUBE gives us more records than would we see with the other examples. We see the grand total, the total by Country, and the total by each Country and State/Province combo. We also get additional rows for a sum by each State/Province value (which in this case will be the same as the Country, State/Province grouping).

Rollup:

SELECT Country, StateProvince, SUM(SalesCount) as TotalSalesCount 
FROM dbo.Sales
GROUP BY ROLLUP(Country, StateProvince)
ORDER BY Country, StateProvince;

Rollup results:

Country StateProvince SalesCount
NULL NULL 40
Canada NULL 10
Canada British Columbia 3
Canada Ontario 7
US NULL 30
US Georgia 11
US Texas 19

We can see the ROLLUP gives us the same results that we got with the GROUPING SETS example. We get a grand total, a total for each country, and a total for each Country and State/Province combination.

Links:

MS SQL Tips

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

    Country StateProvince SalesCount