CUBE, ROLLUP In SQL SERVER

I learn about CUBE and ROLLUP function today and I found that these operator 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 me give you one 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


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