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 |
| Country | StateProvince | SalesCount |
|---|
SQL Rob 