Microsoft SQL Server 2012 Bible

Grouping Within a Result Set

Aggregate functions are all well and good, but how often do you need a total for an entire table? Most aggregate requirements include a date range, department, type of sale, region, or 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 writing 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 automatically 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 first column of this query returns the CountryRegionCode column. Although ...