Yonah's SQL Tip #3 - 2 tricks with CUBE and ROLLUP

Last week we talked about the CUBE and ROLLUP operators for SQL. Of course, they are very versatile, but are sometimes hard to work with. In this week's SQL Tip, we are going to focus on two things - providing custom group names for aggregates and altering the sort order so that aggregates come last.

First Let's Start with this query aginst the pubs database that ships with SQL server:

select type,title,avg(price)
from titles
group by type,title
with rollup


The results of course, will show the type, title, and average price for each entry in the titles table (of course avg is meaningless since each title only has one price, but you get the point). We will also see that for each ‘type’ there is one additional entry that has a NULL value in the title field. This entry is the average price for all of the titles of a specific type. Of course, the NULL is unsightly. It would be much more user-friendly, if the title field contained a useful phrase like Average for all titles or Average for all type titles where type is the specific type.

This is very easy to do, using the isNull() function:

select type,isNull(title,’Average for all ‘+type+’ titles’) as title,avg(price)
from titles
group by type,title
with rollup


With this small change we now have a pretty name for our ‘rolled-up’ field. However, we still have a problem with sorting – because if we sort by title – the Averages will come up close to the top of the list. So now we need a little trick to get them to sort properly. That’s where we can make use of a handy little case statement:


select type,isNull(title,'Average for all '+type+' titles') as title,avg(price),
case when title is null then 1 else 0 end as sort_order
from titles
group by type,title
with rollup
order by type,sort_order


This will effectively sort the totals for each type to appear below all of its titles. Of course, lets say we want to put all of the totals at the bottom, we can simply change the last line to read:

order by sort_order, type

By reversing the order of the columns, we now ensure that all specific titles appear first, before the type summaries.