Grouping Sets

GROUPING SETS in MSSQL 2008 is an Extension to “Group By” and Gives ease of use and flexibility to define grouping expressions as compared to “RollUp” and “Cube”.

The advantage of using GROUPING SETS is that you can define grouping expressions in Grouping Sets.

Example

SELECT CASE WHEN CountryName IS Null THEN 'All Country' ELSE CountryName End AS CountryName,  

CASE WHEN StateName Is Null THEN 'Total' ELSE StateName END AS StateName, 

COUNT(CityName) CityCount  

FROM CityMaster Group By GROUPING Sets  

(

(CountryName,StateName),CountryName,()  

)  

Order by CountryName desc,StateName Desc