Wikis - Page Details

First published by Sugumar Pannerselvam
When: 5 Aug 2013 4:07 AM
Last revision by Ed Price - MSFT (aMicrosof)
When: 7 Sep 2013 9:51 AM
Revisions: 12
Comments: 10

Options

Can You Improve This Article?

Positively! Click Sign In to add the tip, solution, correction or comment that will help other users.

Report inappropriate content using these instructions.

SQL Server 2008+: Group By with Wings

This article includes information about a feature from SQL Server 2008 (and up) - Group by extensions.

Problem Definition
Solution

Problem Definition

In SQL Server 2005 and earlier versions, we would have faced the situation that to get sum or avg or max based on more than one column.

The only way to go, GROUP BY with multiple queries and Union them at final which leads to performance issues.

Solution

SQL Server 2008 added new wings to the Group By clause to do multiple GroupBy on the single query.

— New Wings of Group By:

◦ GROUPING SETS,

◦ CUBE

◦ ROLLUP

@Population table variable script

The below examples are using @Population table variable. The script mentioned below for the table variable generation.

DECLARE @Population TABLE (Country VARCHAR(100),[State] VARCHAR(100),City VARCHAR(100),Population Decimal (6,1))
INSERT INTO @Population VALUES('India', 'Delhi','East Delhi',9 )
INSERT INTO @Population VALUES('India', 'Delhi','South Delhi',8 )
INSERT INTO @Population VALUES('India', 'Delhi','North Delhi',5.5)
INSERT INTO @Population VALUES('India', 'Delhi','West Delhi',7.5)
INSERT INTO @Population VALUES('India', 'Karnataka','Bangalore',9.5)
INSERT INTO @Population VALUES('India', 'Karnataka','Belur',2.5)
INSERT INTO @Population VALUES('India', 'Karnataka','Manipal',1.5)
INSERT INTO @Population VALUES('India', 'Maharastra','Mumbai',30)
INSERT INTO @Population VALUES('India', 'Maharastra','Pune',20)
INSERT INTO @Population VALUES('India', 'Maharastra','Nagpur',11 )
INSERT INTO @Population VALUES('India', 'Maharastra','Nashik',6.5)

Grouping Sets

Simply list all grouping sets that you need

SELECT Country,[State],City,

SUM (Population) AS [Population]

FROM @Population

GROUP BY GROUPING SETS ((Country,[State]),([State],City))

Cube:

It produces all possible grouping sets that can be formed out of the elements listed in parentheses, including the empty grouping set

SELECT Country,[State],City,

SUM (Population) AS [Population]

FROM @Population

GROUP BY Country,[State],City WITH CUBE

Roll Up

The ROLLUP sub clause produces only the grouping sets that have business value, assuming a hierarchy between the elements.

SELECT Country,[State],City,

SUM (Population) AS [Population]

FROM @Population

GROUP BY Country,[State],City WITH ROLLUP

Conclusion
Use the wings to fly on performance.

-Sugumar Pannerselvam

References

http://technet.microsoft.com/en-us/library/bb522495(v=sql.100).aspx
http://www.sqlserver-training.com/grouping-sets-sql-server-2008-video-tutorial/