Friday, October 21, 2011

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.


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  
Order by CountryName desc,StateName Desc  

No comments:

Post a Comment