Microsoft® SQL Server® 2012 Bible

254

Part II: Building Databases and Working with Data

USE AdventureWorks;

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

Year(DueDate) ,st.[Group]

ORDER BY Year(DueDate)

Result:

DueYear Group TotalSalesAmount

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

2005 Europe 726467.4426

2005 North America 10304264.4596

2005 Pacific 1353785.5482

2006 Europe 3686361.9651

2006 North America 28468091.6318

2006 Pacific 2388704.7024

2007 Europe 9954422.8419

2007 North America 32492706.9613

2007 Pacific 4232931.026

2008 Europe 7806365.3801

2008 North America 17963729.3383

2008 Pacific 3838954.8186

For the purposes of a GROUP BY, null values are considered equal to other nulls and are grouped together into a

single result row.

Grouping Sets

Normally, SQL Server groups by every unique combination of values in every column listed

in the GROUP BY clause. The ability of grouping sets is a variation of what was introduced

in SQL Server 2008. With grouping sets, a summation row is generated for each unique

value in each set. You can think of grouping sets as executing several GROUP BY queries

(one for each grouping set) and then combining, or unioning, the results.

For example, the following two queries produce the same result. The fi rst query uses two

GROUP BY queries which are combined into one result set using a UNION; the second query

uses the new grouping set feature:

USE AdventureWorks;

SELECT

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

http://www.it-ebooks.info