Join the Discussion "Share your questions and  tap the knowledge of hundreds of your peers." Mike Chapple

Related Resources • SQL Fundamentals • Oracle Resources • Choosing a Database

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

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:

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:

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!