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:
- Grouping set: a set of group by columns.
- Helps support dynamic analysis of aggregates.
- SQL Server versions earlier than 2008 had limited support for grouping sets.
- Produces a single result set that is equivalent to a
UNION ALL of differently grouped rows.
- Makes aggregation querying and reporting easier and faster.
Example
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
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
| You must Sign In to use this message board. |
|
|
|
|
