SQL 301 — WITH CUBE and ROLLUP

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!

Leave a Reply

Your email address will not be published. Required fields are marked *