GROUPING SETS Clause in SQL Server -

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.