Grouping Sets in MS SQL Server 2008

Introduction

One of the fantastic new features of SQL Server 2008 is the Grouping Sets. Grouping Sets is an extension to the GROUP BY clause that lets users define multiple groups in the same query.

Facts about Grouping Sets

Here are a few facts that you must know before starting to use Grouping Sets:

1. Grouping set: a set of group by columns.
2. Helps support dynamic analysis of aggregates.
3. SQL Server versions earlier than 2008 had limited support for grouping sets.
4. Produces a single result set that is equivalent to a UNION ALL of differently grouped rows.
5. Makes aggregation querying and reporting easier and faster.

Example

-- Use UNION ALL on dual SELECT statements
SELECT CustomerType, Null AS TerritoryID, MAX(ModifiedDate)
FROM dbo.tbl_Customer GROUP BY CustomerType
UNION ALL
SELECT Null AS CustomerType, TerritoryID, MAX(ModifiedDate)
FROM dbo.tbl_Customer GROUP BY TerritoryID
ORDER BY TerritoryID

-- Use GROUPING SETS on single SELECT statement
SELECT CustomerType, TerritoryID, MAX(ModifiedDate)
FROM dbo.tbl_Customer
GROUP BY GROUPING SETS ((CustomerType), (TerritoryID)) 
ORDER BY CustomerType

SELECT 
       d.Year, d.Quarter, t.Country, SUM(f.SalesAmount) AS SalesAmount
FROM 
       dbo.tblSales AS f INNER JOIN 
       dbo.tblSDate AS d ON f.SDateID = d.SDateID INNER JOIN 
       dbo.tblSTerritory AS t ON f.STerritoryID = t.STerritoryID
WHERE 
       d.Year IN (2006, 2007)
       GROUP BY GROUPING SETS ( 
                (Year, Quarter, Country), 
                (Year, Quarter) ,
                (Country), 
                () )
       ORDER BY 
                Year, Quarter, Country