Click here to Skip to main content
13,355,335 members (72,988 online)
Click here to Skip to main content
Add your own
alternative version


15 bookmarked
Posted 10 Jun 2009

Grouping Sets in MS SQL Server 2008

, 10 Jun 2009
Rate this:
Please Sign up or sign in to vote.
New feature - SQL Server 2008 Grouping Sets.


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.


-- Use UNION ALL on dual SELECT statements
SELECT CustomerType, Null AS TerritoryID, MAX(ModifiedDate)
FROM dbo.tbl_Customer GROUP BY CustomerType
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

       d.Year, d.Quarter, t.Country, SUM(f.SalesAmount) AS SalesAmount
       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
       d.Year IN (2006, 2007)
                (Year, Quarter, Country), 
                (Year, Quarter) ,
                () )
       ORDER BY 
                Year, Quarter, Country


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

You may also be interested in...


Comments and Discussions

GeneralNice Pin
Md. Marufuzzaman15-Jan-10 0:52
mvpMd. Marufuzzaman15-Jan-10 0:52 
GeneralRe: Nice Pin
Robin_Roy17-Jan-10 17:04
mvpRobin_Roy17-Jan-10 17:04 
GeneralRe: Nice Pin
Md. Marufuzzaman17-Jan-10 18:43
mvpMd. Marufuzzaman17-Jan-10 18:43 
GeneralMy Vote of 5 Pin
johnclark6410-Aug-09 18:03
memberjohnclark6410-Aug-09 18:03 
GeneralRe: My Vote of 5 Pin
Robin_Roy17-Aug-09 21:55
memberRobin_Roy17-Aug-09 21:55 
GeneralBetter if you showed the output as well. A picture tells.... Pin
robvon16-Jun-09 16:48
memberrobvon16-Jun-09 16:48 
Smile | :)
GeneralRe: Better if you showed the output as well. A picture tells.... Pin
Robin_Roy17-Aug-09 21:56
memberRobin_Roy17-Aug-09 21:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.180111.1 | Last Updated 11 Jun 2009
Article Copyright 2009 by Robin_Roy
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid