Aggregate Functions and the GROUP BY Clause

Aggregate functions can be applied to all rows in a table, to a subset of the table specified by a WHERE clause, or to one or more groups of rows in the table. A single value is generated from each set of rows to which an aggregate function is applied.

This example calculates the sum of year-to-date sales for all books in the titles table:

SELECT SUM(ytd_sales)
FROM titles



-------------

97446



(1 row(s) affected)


The optional keyword DISTINCT can be used with SUM, AVG, and COUNT to eliminate duplicate values before an aggregate function is applied. For SUM, AVG, and COUNT, ALL is the default.

Important The output for statements involving MIN or MAX on char or varchar columns depends on the sort order chosen during installation. For information about the effects of different sort orders, see Microsoft SQL Server Setup.

For example, with this query you can find the average price of all books if the prices were doubled:

SELECT avg(price * 2)
FROM titles



------------

47.88



(1 row(s) affected)


The syntax of the aggregate functions and their results are as follows:

Aggregate function Result
SUM([ALL | DISTINCT] expression) Total of the values in the numeric expression, either all or distinct
AVG([ALL | DISTINCT] expression) Average of the values in the numeric expression, either all or distinct
COUNT([ALL | DISTINCT] expression) Number of values in the expression, either all or distinct
COUNT(*) Number of selected rows
MAX(expression) Highest value in the expression
MIN(expression) Lowest value in the expression

SUM, AVG, COUNT, MAX, and MIN ignore null values; COUNT(*) does not.

Aggregate functions can be used in a select list, as in the preceding examples, or in the HAVING clause of a SELECT statement that includes a GROUP BY clause. For more information, see The HAVING Clause.

Aggregate functions cannot be used in a WHERE clause. However, a SELECT statement with aggregate functions in its select list often includes a WHERE clause that restricts the rows to which the aggregate function is applied. In the examples given earlier, each aggregate function produces a single summary value for the whole table. If a SELECT statement includes a WHERE clause (but not a GROUP BY clause), an aggregate function produces a single value for the subset of rows that the WHERE clause specifies.

This query returns the average advance and the sum of year-to-date sales for business books only:

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

---------     -------
6,281.25      30788

(1 row(s) affected)

Whenever an aggregate function is used in a SELECT statement that does not include a GROUP BY clause, it produces a single value. This is true whether it is operating on all rows in a table or on a subset of rows defined by a WHERE clause. Such a function is called a scalar aggregate.

Note that you can use more than one aggregate function in the same select list and produce more than one scalar aggregate in a single SELECT statement.