Have you been asked to summarize some data into a nice report for management? They want to see subtotals and grand totals you say?
You don’t have to drop the results of your GROUP BY statement into a temp table, and summarize the subtotals yourself. The SQL ROLLUP and CUBE commands offer a valuable tool for gaining some quick and easy insight into your data.
WITH ROLLUP you’ll get a subtotal for each combination of grouped columns, from right to left. So you can get multiple outputs of a ROLLUP statement, based on the order you enter your columns into your select and GROUP BY statements. Make sure you enter them in least restrictive to most restrictive.
Think hierarchy, and you’ll be ok!
WITH CUBE is more complex. It will give you summary for each combination of the columns you enter. I’ll show you an example later to make this a bit more clear.
Let’s return to our KOTOR database. It’s a database I use to store data about items in the game Knights of the Old Republic. In that database suppose the following data exists:
Items for sale:
| Type | Store | Number |
| Blaster | Tatooine | 12 |
| Shield | Tatooine | 18 |
| Lightsaber Crystal | Dantooine | 4 |
| Blaster | Dantooine | 14 |
| Shield | Korriban | 9 |
| Blaster | Korriban | 5 |
| Lightsaber Crystal | Korriban | 1 |
As the proud owners of these shops, we’d like to take a quick look at various aspects of our inventory. Let’s take a look at the data using WITH CUBE:
SELECT Type, Store, SUM(Number) as Number FROM items GROUP BY type,store WITH CUBE
And the results of the query:
| Type | Store | Number |
| Shield | Tatooine | 18 |
| Shield | Korriban | 9 |
| Shield | NULL | 27 |
| Blaster | Tatooine | 12 |
| Blaster | Korriban | 5 |
| Blaster | Dantooine | 14 |
| Blaster | NULL | 31 |
| Lightsaber Crystal | Korriban | 1 |
| Lightsaber Crystal | Dantooine | 4 |
| Lightsaber Crystal | NULL | 5 |
| NULL | NULL | 63 |
| NULL | Tatooine | 30 |
| NULL | Korriban | 15 |
| NULL | Dantooine | 18 |
Wow! That’s a lot of data! Notice that we are presented with a number of additional groupings that contain NULL fields that wouldn’t appear in the results of a normal GROUP BY command. These are the summarization rows added by the CUBE statement. Analyzing the data, you’ll notice that our chain has 27 Shields, 31 Blasters and 5 Lightsaber Crystals spread among our three stores. Our Tatooine store has the largest number of items in stock with a whopping inventory of 30 items.
We’re not particularly interested in the total number of items at each store — we’d just like to know our galaxy-wide inventory of each item along with the standard GROUP BY data. Utilizing the ROLLUP operator instead of the CUBE operator will eliminate the results that contain a NULL in the first column.
Here’s the SQL:
SELECT Type, Store, SUM(Number) as Number FROM items GROUP BY type,store WITH ROLLUP
And the results:
| Type | Store | Number |
| Shield | Tatooine | 18 |
| Shield | Korriban | 9 |
| Shield | NULL | 27 |
| Blaster | Tatooine | 12 |
| Blaster | Korriban | 5 |
| Blaster | Dantooine | 14 |
| Blaster | NULL | 31 |
| Lightsaber Crystal | Korriban | 1 |
| Lightsaber Crystal | Dantooine | 4 |
| Lightsaber Crystal | NULL | 5 |
| NULL | NULL | 63 |
And that’s CUBE and ROLLUP in a nutshell! There’s plenty more to go, I need to show you the GROUPING function and the GROUPING SETS feature. I’ll get to those as soon as I can. If you have any questions, please let me know!