Aggregate Functions

Return summary values. Aggregate functions calculate summary values, such as averages and sums, from the values in a particular column, and they return a single value for each set of rows to which the function applies.

Aggregate functions often appear with GROUP BY, which partitions a table into groups. These functions calculate a single value for each group. Without GROUP BY, an aggregate function in the select list produces a single value as a result, whether it is operating on all the rows in a table or on a subset of rows defined by a WHERE clause.

Syntax

aggregate_ function ([ALL | DISTINCT] expression)

where

aggregate_ function

Specifies an aggregate function.

The aggregate functions are:

Aggregate function	Description
AVG	Returns the average of all the values, or only the DISTINCT values, in the expression. AVG can be used with numeric columns only. Null values are ignored.
COUNT	Returns the number of non-null values in the expression. When DISTINCT is specified, COUNT finds the number of unique non-null values. COUNT can be used with both numeric and character columns. Null values are ignored.
COUNT(*)	Returns the number of rows. COUNT(*) takes no parameters and cannot be used with DISTINCT. All rows are counted, even those with null values.
MAX	Returns the maximum value in the expression. MAX can be used with numeric, character, and datetime columns, but not with bit columns. With character columns, MAX finds the highest value in the collating sequence. MAX ignores any null values. DISTINCT is available for ANSI compatibility; but, it is not meaningful with MAX.
MIN	Returns the minimum value in the expression. MIN can be used with numeric, character, and datetime columns, but not with bit columns. With character columns, MIN finds the value that is lowest in the sort sequence. MIN ignores any null values. DISTINCT is available for ANSI compatibility; but, it is not meaningful with MIN.
SUM	Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored.
Numeric columns refer to decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint datatypes.

ALL

Applies the aggregate function to all values. ALL is the default.

DISTINCT

Eliminates duplicate values before an aggregate function is applied. DISTINCT is optional with SUM, AVG, COUNT, MIN, MAX and cannot be used with COUNT(*). Although available for ANSI compatibility, DISTINCT is meaningless with MIN and MAX.

expression

Is a column name, constant, function, any combination of column names, constants, and functions connected by arithmetic or bitwise operators. For more information, see the Expressions topic.

Remarks

Aggregate functions, which calculate summary values from the non-null values in a particular column, can be applied to all rows in a table. In this case, they produce a single value called a scalar aggregate function. Or they can be applied to all rows that have the same value in a column or columns or expression (with the GROUP BY and, optionally, the HAVING clause). In this case, they produce a value for each group, called a vector aggregate function. The results of the aggregate functions are shown as new columns.

When you sum or average numeric data, Transact-SQL promotes the resulting datatype to the minimum precision and scale necessary to hold the result. To avoid overflow errors, declare all variables that will hold the result of a sum or average with the most precise datatype of the values supplied. For example, when summing tinyint and int values, use a resulting variable of type int. When working with datatypes of varying precision and scale, use the maximum precision and the maximum scale.

Examples

A.    SUM and AVG Functions

This example calculates the average advance and the sum of year-to-date sales for all business books. Each of these aggregate functions produces a single summary value for all of the retrieved rows.

SELECT AVG(advance), SUM(ytd_sales)
    FROM titles
        WHERE type = 'business'

B.    SUM and AVG Function with a GROUP BY Clause

When used with a GROUP BY clause, aggregate functions produce single values for each group, rather than for the whole table. This example produces summary values for each type of book.

SELECT type, AVG(advance), SUM(ytd_sales)
    FROM titles
        GROUP BY type

C.    COUNT of DISTINCT

This example finds the number of different cities in which authors live.

SELECT COUNT(DISTINCT city)
    FROM authors

D.    GROUP BY HAVING COUNT(*)

This example lists the types in the titles table but eliminates the types that include more than one book.

SELECT type
    FROM titles
        GROUP BY type
            HAVING COUNT(*) > 1

E.    HAVING SUM and AVG

This example groups the titles table by publisher and includes only those groups of publishers who have paid more than $25,000 in total advances and whose books average more than $15 in price.

SELECT pub_id, SUM(advance), AVG(price)
    FROM titles
        GROUP BY pub_id
            HAVING SUM(advance) > $25000 
                AND AVG(price) > $15