Grouping Sets provide us more flexibility unlike CUBE and ROLLUP that we discussed in previous blogs. While using GROUPING SETS we could specify each group we want to aggregate our data.
If we have Col1, Col2 and Col3 in Group BY and we want to aggregate our data by {Col1, Col3},{Col2, Col3},{Col3} we could use GROUPING SETS as
SELECT Col1, Col2,Col3, COUNT(*) From Table1 Group By GROUPING SETS( (Col1, Col3),(Col2, Col3),(Col3)); |
Then the result of aggregate will be summarized as
col1,col3,Count(*)
col2,col3,Count(*)
col3,Count(*)
Let us take one practical example on AdventureWorks database.
--GROUPING SETS example SELECT t.[group] AS Region, t.countryregioncode AS Country, t.name AS TerritoryName, Sum(sh.totaldue) TotalSales FROM sales.salesorderheader sh JOIN sales.salesterritory t ON sh.territoryid = t.territoryid WHERE t.[group] = 'Europe' GROUP BY grouping sets( ( t.[group], t.countryregioncode ), ( t.countryregioncode, t.name ), ( t.name ) ) ORDER BY t.[group] DESC, t.countryregioncode DESC, t.name DESC; go |
I sorted the result so that it will be easy to understand the result set. As you could see above GROUPING SETS clause gives us more flexibility to group our columns.
Read More
http://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx