Beginning Transact-SQL with SQL Server 2000 and 2005

7

Aggregation and Grouping

Information is meaningful; data is just values stored in a table. Often, the information part of the equation comes from analyzing groups of records and comparing how one range of records relates to another. For example, rather than viewing individual sales records, you may be interested in comparing the total sales of a product in one region to another or, perhaps, the average price of mountain bike sales with the average price of road bike sales.

The term aggregation refers to something that is a part of something else. In this context, an aggregate function returns a single value for a group of records. You can use aggregate functions in two different ways. You can “roll up” or summarize all of the rows returned by a query (either all records or use filtering techniques as discussed in Chapter 5). Aggregation can also be applied at a group level, showing summarized values for the rows having the same values in the columns you designate for grouping.

Using Aggregate Functions

The simplest technique is aggregating all rows in a query. Aggregate functions include the means to summarize a range of values in a variety of ways. You may simply want to count the rows that match a criterion or get the sum of a range of numeric values. The following table contains all of the system-supplied aggregate functions supported by Transact-SQL used to summarize column values.

Function	Description
COUNT()	Calculates the count of all non-null values for a specific ...