Home > SQL Server 2005, SQL Server 2008 > CUBE, ROLLUP, COMPUTE, COMPUTE BY, GROUPING SETS

CUBE, ROLLUP, COMPUTE, COMPUTE BY, GROUPING SETS


The CUBE and ROLLUP operators are useful in generating reports that contain subtotals and totals. There are extensions of the GROUP BY clause.

Difference b/w CUBE and ROLLUP:
– CUBE generates a result set that shows aggregates for all combinations of values in the selected columns.
– ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.

select 'A' [class], 1 [rollno], 'a' [section], 80 [marks], 'manoj' stuName
into #tempTable
UNION
select 'A', 2, 'a', 70 ,'harish'
UNION
select 'A', 3, 'a', 80 ,'kanchan'
UNION
select 'A', 4, 'b', 90 ,'pooja'
UNION
select 'A', 5, 'b', 90 ,'saurabh'
UNION
select 'A', 6, 'b', 50 ,'anita'
UNION
select 'B', 1, 'a', 60 ,'nitin'
UNION
select 'B', 2, 'a', 50 ,'kamar'
UNION
select 'B', 3, 'a', 80 ,'dinesh'
UNION
select 'B', 4, 'b', 90 ,'paras'
UNION
select 'B', 5, 'b', 50 ,'lalit'
UNION
select 'B', 6, 'b', 70 ,'hema'

select class, rollno, section, marks, stuName from #tempTable
Output:
class	rollno	section	marks	stuName
A	1	a	80	manoj
A	2	a	70	harish
A	3	a	80	kanchan
A	4	b	90	pooja
A	5	b	90	saurabh
A	6	b	50	anita
B	1	a	60	nitin
B	2	a	50	kamar
B	3	a	80	dinesh
B	4	b	90	paras
B	5	b	50	lalit
B	6	b	70	hema

WITH ROLLUP:

select class, section, sum(marks) [sum]
from #tempTable
group by class, section with ROLLUP
Output:
class	section	sum
A	a	230
A	b	230
A	NULL	460  -- 230 + 230  = 460
B	a	190
B	b	210
B	NULL	400  -- 190 + 210 = 400
NULL	NULL	860  -- 460 + 400 = 860 

WITH CUBE:

select class, section, sum(marks) [sum]
from #tempTable
group by class, section with CUBE
Output:
class	section	sum
A	a	230
A	b	230
A	NULL	460  -- 230 + 230  = 460
B	a	190
B	b	210
B	NULL	400  -- 190 + 210 = 400
NULL	NULL	860  -- 460 + 400 = 860
NULL	a	420  -- 230 + 190 = 420
NULL	b	440  -- 230 + 210 = 440 

COMPUTE & COMPUTE BY:
A COMPUTE BY clause allows you to see both detail and summary rows with one SELECT statement. You can calculate summary values for subgroups, or a summary value for the whole result set.
The COMPUTE clause takes the following information:
– The optional BY keyword. This calculates the specified row aggregate on a per column basis.
– A row aggregate function name. This includes SUM, AVG, MIN, MAX, or COUNT.
– A column upon which to perform the row aggregate function.

select class, section, marks
from #tempTable
COMPUTE SUM(marks), AVG(marks)

select class, section, marks
from #tempTable
order by class
COMPUTE SUM(marks), AVG(marks) by class

select class, section, marks
from #tempTable
order by class, section
COMPUTE SUM(marks), AVG(marks) by class, section

Final Cleanup, drop the temp tables:

drop table #tempTable

GROUPING SETS:
SQL Server 2008 has a new GROUPING SETS operator which can generate the same result set as that generated by using a simple GROUP BY, ROLLUP, or CUBE operator.
More on GROUPING SETS: http://msdn.microsoft.com/en-us/library/bb522495.aspx

Useful Links:
ROLLUP & CUBE: http://msdn.microsoft.com/en-us/library/ms189305(SQL.90).aspx
COMPUTE & COMPUTE BY: http://msdn.microsoft.com/en-us/library/ms190452(v=SQL.90).aspx

About these ads
  1. Ed Sun
    March 27, 2014 at 12:41 am

    How do you change the label in the output of COMPUTE BY clause? I’d like to change the default label “Sum” to something more meaningful. Thanks.

  1. July 29, 2011 at 9:52 am
  2. November 19, 2012 at 12:37 am
  3. June 30, 2014 at 7:58 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 399 other followers

%d bloggers like this: