SQL Server 2008+: Group By with Wings

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

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

Leave a Comment
  • Please add 4 and 6 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Balaji M Kundalam edited Revision 8. Comment: Typography - minor edit

  • Maheshkumar S Tiwari edited Revision 6. Comment: Added TOC and formatting

Page 1 of 1 (2 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • You may want to add @Population table script and some data if you want your readers to try these queries

  • Added the script for @Population table.

    Thanks for your suggestion Naomi.

  • The column name in the DECLARE statement ([Population (in Millions)]) doesn't match the column name used in the queries (Population).

    Nice concise description and examples.

  • I think References is a must on short WIKI articles. you can add some references for nice articles on the same or similar topics.

    This topic described fully in Microsoft's BOL (Book On Line)

    Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS

    technet.microsoft.com/.../bb522495(v=sql.100).aspx

  • I think References is a must on short WIKI articles. you can add some references for nice articles on the same or similar topics.

    This topic described fully in Microsoft's BOL (Book On Line)

    Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS

    technet.microsoft.com/.../bb522495(v=sql.100).aspx

  • Thanks Gert and Pituach. Article updated with your comments.

  • Very good article!

  • Maheshkumar S Tiwari edited Revision 6. Comment: Added TOC and formatting

  • Good article to make an entry....Welcome!!!!!!!!

  • Balaji M Kundalam edited Revision 8. Comment: Typography - minor edit

Page 1 of 1 (10 items)