Microsoft® SQL Server® 2012 Bible

255

Chapter 10: Aggregating, Windowing, and Ranking Data

10

NULL AS DueYear,

st.[Group],

SUM(TotalDue) TotalSalesAmount

FROM Sales.SalesOrderHeader soh

INNER JOIN Sales.SalesTerritory st

ON soh.TerritoryID = st.TerritoryID

GROUP BY

st.[Group]

UNION

SELECT

Year(DueDate) DueYear,

NULL [Group],

SUM(TotalDue) TotalSalesAmount

FROM Sales.SalesOrderHeader soh

INNER JOIN Sales.SalesTerritory st

ON soh.TerritoryID = st.TerritoryID

GROUP BY

Year(DueDate)

SELECT

Year(DueDate) DueYear,st.[Group],

SUM(TotalDue) TotalSalesAmount

FROM Sales.SalesOrderHeader soh

INNER JOIN Sales.SalesTerritory st

ON soh.TerritoryID = st.TerritoryID

GROUP BY

GROUPING SETS(

Year(DueDate) ,st.[Group])

Result (same for both queries):

DueYear Group TotalSalesAmount

----------- --------------- ---------------------

NULL Europe 2173617.6297

NULL North America 89228792.391

NULL Pacific 11814376.0952

2005 NULL 12384517.4504

2006 NULL 34543158.2993

2007 NULL 46680060.8292

2008 NULL 9609049.537

The Grouping Sets syntax is considerably more simple and precise. It provides the abil-

ity to directly specify the needed aggregations.

Filtering Grouped Results

When combined with grouping, fi ltering can be a problem. Are the row restrictions applied

before the GROUP BY or after the GROUP BY? Some databases use nested queries to

c10.indd 255c10.indd 255 7/30/2012 4:26:13 PM7/30/2012 4:26:13 PM

http://www.it-ebooks.info