Summarizing Data with CUBE and ROLLUP

Looking for a quick, efficient way to summarize the data stored in your database?  The SQL ROLLUP and CUBE commands offer a valuable tool for gaining some quick and dirty insight into your data.  ROLLUP and CUBE are SQL extensions and they're available in SQL Server 6.5 (and above) and Oracle 8i (and above).

The CUBE command is added to an SQL 

To provide an example, let's imagine a table that contains the number and type of pets available for sale at our chain of pet stores:

Pets

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

As the proud owners of this Florida pet superstore, we'd like to take a quick look at various aspects of our inventory.  We could hire an SQL programmer to sit down and write a number of queries to retrieve the exact data that we're looking for.  However, our dataset isn't very large and we enjoy looking at the raw numbers.  Our hunger for data can be appeased using the CUBE command.  Here's the sample SQL:

SELECT Type, Store, SUM(Number) as Number
FROM Pets 
GROUP BY type,store 
WITH CUBE

And the results of the query:

Type	Store	Number
Cat	Miami	18
Cat	Naples	9
Cat	NULL	27
Dog	Miami	12
Dog	Naples	5
Dog	Tampa	14
Dog	NULL	31
Turtle	Naples	1
Turtle	Tampa	4
Turtle	NULL	5
NULL	NULL	63
NULL	Miami	30
NULL	Naples	15
NULL	Tampa	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 cats, 31 dogs and 5 turtles spread among our three stores.  Our Miami store has the largest number of pets in stock with a whopping inventory of 30 pets.

We're not particularly interested in the total number of pets at each store -- we'd just like to know our statewide inventory of each species 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 Pets 
GROUP BY type,store 
WITH ROLLUP

And the results:

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

And that's CUBE and ROLLUP in a nutshell!  Be sure to check back next week for another exciting journey into the world of databases!