Advertisements
Home > SQL Server 2008 > Using GROUPING SETS | SQL Server 2008 and above

Using GROUPING SETS | SQL Server 2008 and above


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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: