This article includes information about a feature from SQL Server 2008 (and up) - Group by extensions.
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.
— New Wings of Group By:
◦ GROUPING SETS,
◦ CUBE
◦ ROLLUP
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))
It produces all possible grouping sets that can be formed out of the elements listed in parentheses, including the empty grouping set
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.
GROUP BY Country,[State],City WITH ROLLUP
Balaji M Kundalam edited Revision 8. Comment: Typography - minor edit
Maheshkumar S Tiwari edited Revision 6. Comment: Added TOC and formatting
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
Thanks Gert and Pituach. Article updated with your comments.
Very good article!
Good article to make an entry....Welcome!!!!!!!!