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.
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
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 ...