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.

Let’s check this by a simple example:

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: (this feature is no longer supported and discontinued with SQL Server 2012 and next versions)

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.

–> Grouping Sets for SQL Server 2008 and above, check here.

–> Grouping Sets equivalent for SQL Server 2005 and below, check here.
 

>> Check & Subscribe my [YouTube videos] on SQL Server.
 

---