Professional SQL Server™ 2005 Programming

A.2. Aggregate Functions

Aggregate functions are applied to sets of records rather than a single record. The information in the multiple records is processed in a particular manner and then is displayed in a single record answer. Aggregate functions are often used in conjunction with the GROUP BY clause.

The aggregate functions are:

• AVG

• CHECKSUM

• CHECKSUM_AGG

• COUNT

• COUNT_BIG

• GROUPING

• MAX

• MIN

• STDEV

• STDEVP

• SUM

• VAR

• VARP

In most aggregate functions, the ALL or DISTINCT keywords can be used. The ALL argument is the default and will apply the function to all the values in the expression, even if a value appears numerous times. The DISTINCT argument means that a value will only be included in the function once, even if it occurs several times.

Aggregate functions cannot be nested. The expression cannot be a subquery.

A.2.1. AVG

AVG returns the average of the values in expression. The syntax is as follows:

AVG([ALL | DISTINCT] <expression>)

The expression must contain numeric values. NULL values are ignored.

A.2.2. COUNT

COUNT returns the number of items in expression. The data type returned is of type int. The syntax is as follows:

COUNT
(
   [ALL | DISTINCT] <expression> | *
)

The expression cannot be of the uniqueidentifier, text, image, or ntext data types. The * argument returns the number of rows in the table; it does not eliminate duplicate or NULL values.

A.2.3. COUNT_BIG

COUNT_BIG returns the number of items in a group. This is very similar to the COUNT function described above, with the ...