SQL with Manoj

In my previous posts (long back) I discussed about using [CUBE and ROLLUP] operators introduced in SQL Server 2008. I also discussed about COMPUTE & COMPUTE BY operators, but there two were removed from the SQL Server 2012 version.

Here in this post I’ll talk about GROUPING SETS (few days back my friend asked me about this and I thought its better to document this here).

Just like CUBE and ROLLUP, GROUPING SETS are used to GROUP and calculate Sub Totals and Totals within a set of records. Compared to ROLLUP with GROUPING SETS you have more flexibility to show Sub Totals on selected columns.

-- ROLLUP
SELECT class, section, rollno, sum(marks) [sum]
FROM #tempTable
GROUP BY class, section, rollno WITH ROLLUP

Output:
class		section	rollno	sum
HighSchool	a	1	80
HighSchool	a	2	70
HighSchool	a	3	80
HighSchool	a	NULL	230
HighSchool	b	4	90
HighSchool	b	5	90
HighSchool	b	6	50
HighSchool	b	NULL	230
HighSchool	NULL	NULL	460
Intermediate	a	1	60
Intermediate	a	2	50
Intermediate	a	3	80
Intermediate	a	NULL	190
Intermediate	b	4	90
Intermediate	b	5	50
Intermediate	b	6	70
Intermediate	b	NULL	210
Intermediate	NULL	NULL	400
NULL		NULL	NULL	860

–> Now, if you want to just Sub Total on Class level, you just need to use GROUPING SET operator like this:

-- GROUPING SETS on selective columns
SELECT class, section, rollno, sum(marks) [sum]
FROM #tempTable
GROUP BY GROUPING SETS (
	 (class, section, rollno)
	,(class)
)

This will give you more controlled Sub Totaling and grouping on selected columns:

Output:
class		section	rollno	sum
HighSchool	a	1	80
HighSchool	a	2	70
HighSchool	a	3	80
HighSchool	b	4	90
HighSchool	b	5	90
HighSchool	b	6	50
HighSchool	NULL	NULL	460
Intermediate	a	1	60
Intermediate	a	2	50
Intermediate	a	3	80
Intermediate	b	4	90
Intermediate	b	5	50
Intermediate	b	6	70
Intermediate	NULL	NULL	400

–> You can also get the same output like ROLLUP operator, but you have to provide columns in following groups:

-- GROUPING SETS
SELECT class, section, rollno, sum(marks) [sum]
FROM #tempTable
GROUP BY GROUPING SETS (
	 (class, section, rollno)
	,(class, section)
	,(class)
	,()
)

Note: You can also use GROUPING SETS with combination of ROLUP and/or CUBE operators, check more on regarding this on MS BoL, here.

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