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
B	a	190
B	b	210
B	NULL	400
NULL	NULL	860

WITH CUBE:

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

from #tempTable

group by class, section with CUBE

Output

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

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.