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.

01	select 'A' [class], 1 [rollno], 'a' [section], 80 [marks], 'manoj' stuName

02	into #tempTable

03

UNION

04	select 'A', 2, 'a', 70 ,'harish'

05

UNION

06	select 'A', 3, 'a', 80 ,'kanchan'

07

UNION

08	select 'A', 4, 'b', 90 ,'pooja'

09

UNION

10	select 'A', 5, 'b', 90 ,'saurabh'

11

UNION

12	select 'A', 6, 'b', 50 ,'anita'

13

UNION

14	select 'B', 1, 'a', 60 ,'nitin'

15

UNION

16	select 'B', 2, 'a', 50 ,'kamar'

17

UNION

18	select 'B', 3, 'a', 80 ,'dinesh'

19

UNION

20	select 'B', 4, 'b', 90 ,'paras'

21

UNION

22	select 'B', 5, 'b', 50 ,'lalit'

23

UNION

24	select 'B', 6, 'b', 70 ,'hema'

25

26	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:

1	select class, section, sum(marks) [sum]

2	from #tempTable

3	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:

1	select class, section, sum(marks) [sum]

2	from #tempTable

3	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.

01	select class, section, marks

02	from #tempTable

03	COMPUTE SUM(marks), AVG(marks)

04

05	select class, section, marks

06	from #tempTable

07	order by class

08	COMPUTE SUM(marks), AVG(marks) by class

09

10	select class, section, marks

11	from #tempTable

12	order by class, section

13	COMPUTE SUM(marks), AVG(marks) by class, section

Final Cleanup, drop the temp tables:

1	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

Categories: