Grouping Sets In Microsoft SQL Server 2008

GROUPING SETS in MSSQL is an Extension to “Group By” and Gives Ease Of Use and Flexibility to Define Grouping Expressions As Compared To “RollUp” and “Cube”

Let’s See The Following Table with Country, State And City Names

Now With The above Table If We Wish To Have Count Of City StateWise And CountryWise and Grant Count Of Cities For All Counties.

In Earlier Versions Of SQL We Used Group By and Union and Cube and Rollup To Achive This Now In SQL Server 2008 We Have GROUPING SETS.

Let’s See How this Helps.

SELECT CountryName,StateName ,COUNT(CityName) CityCount
FROM CityMaster
Group By
GROUPING Sets
(
(CountryName,StateName),CountryName,()
)
Order by CountryName desc,StateName Desc

 

To Replace the Nulls and Making Result More Readable You Can Put Some Conditions To Your Query:  See Following

 

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

 

The Advantage Of Using GROUPING SETS Is That you can define grouping expressions in Grouping Sets.