Microsoft® SQL Server® 2012 Bible

253

Chapter 10: Aggregating, Windowing, and Ranking Data

10

the like. That presents a problem. If the only tool to restrict the aggregate function were the

WHERE clause, then database developers would waste hours replicating the same query, or writ-

ing a lot of dynamic SQL queries and the code to execute the aggregate queries in sequence.

Fortunately, aggregate functions are complemented by the GROUP BY function, which auto-

matically partitions the data set into subsets based on the values in certain columns. When the

data set is divided into subgroups, the aggregate functions are performed on each subgroup.

Simple Groupings

Using the GROUP BY clause to complement the aggregate functions provides the ability to

include descriptive columns to the result. For example, in the following query the GROUP

BY clause is included in the query to slice the data by Country or Region Code:

USE AdventureWorks;

SELECT

st.CountryRegionCode,

SUM(TotalDue) TotalSalesDue

FROM Sales.SalesOrderHeader soh

INNER JOIN Sales.SalesTerritory st

ON soh.TerritoryID = st.TerritoryID

GROUP BY

st.CountryRegionCode

Result:

CountryRegionCode TotalSalesDue

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

AU 11814376.0952

CA 18398929.188

DE 5479819.5755

FR 8119749.346

GB 8574048.7082

US 70829863.203

The fi rst column of this query returns the CountryRegionCode column. Although this

column does not have an aggregate function, it still participates within the aggregate

because that’s the column by which the query is grouped. It may therefore be included in

the result set because, by defi nition, there can be only a single country or region code value

in each group. Each row in the result set summarizes one country or region code, and the

aggregate functions now calculate total sales due for each country or region code.

SQL is not limited to grouping by a column. You can group by an expression, but the exact

same expression must be used in the SELECT list, not the individual columns used to gen-

erate the expression.

Nor is SQL limited to grouping by a single column or expression. Grouping by multiple col-

umns and expressions is quite common. The following query is an example of grouping by

one expression that calculates the year number and the country or region code:

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

http://www.it-ebooks.info