1. Tech

Your suggestion is on its way!

An email with a link to:

http://databases.about.com/od/sql/l/aacuberollup.htm

was emailed to:

Thanks for sharing About.com with others!

Summarizing Data with CUBE and ROLLUP
 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

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!



You can opt-out at any time. Please refer to our privacy policy for contact information.

Discuss in my forum

See More About
Related Video
Using Meta Data for Search
Export Data From an Excel Sheet to a Word Document

©2014 About.com. All rights reserved.