Understanding ROLLUP in SQL

Question

I've figured out CUBE as just generating all the permutations, but I am having trouble with ROLLUP. There don't seem to be any good resources online or in the book I'm reading for explaining SQL for people like me who struggle with it.

My book says that ROLLUP is a special case of the CUBE operator that excludes all cases that don't follow a hierarchy within the results.

I'm not entirely sure what it means, but running it on a table I made kinda produces some useful results.

I made a table from another page on google like this:

Type        Store       Number
Dog     Miami       12
Cat     Miami       18
Turtle   Tampa       4
Dog     Tampa       14
Cat     Naples      9
Dog     Naples      5
Turtle   Naples      1

Then here is query I made:

select store,[type], SUM(number) as Number from pets

group by store, [type]

with rollup

This shows me the number of each type of pet in each store, and total pets in each store, which is kinda cool. If I want to see the query based on pets, I found I have to switch the group by order around so type comes first.

So is rollup based on the first group by clause?

The other question is, I read you use ROLLUP instead of CUBE when you have a year and month column to stop it aggregating the same month across multiple years. I think I understand what this means, but could anyone clarify it? And how do you set it up like this?

Can you use ROLLUP to exclude other combinations of columns as well? My table above is quite simple and the query shows you "pets by store", but if there were other columns, could you include/exclude them from the results?

Answer 1

Best explained through an example. Suppose you group by A, B, C. You then get the following groupings with rollup:

(A, B, C)
(A, B)
(A)
()

So you see that the order is important, as you already found out. If you group by A, C, B, you get the following groupings instead:

(A, C, B)
(A, C)
(A)
()