Using Aggregate Functions in the Select List
Aggregate functions (such as SUM, AVG, COUNT, COUNT(*), MAX, and MIN) generate summary values in query result sets. An aggregate function (with the exception of COUNT(*)) processes all the selected values in a single column to produce a single result value. 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. When an aggregate function is applied, a single value is generated from each set of rows.
This example calculates the sum of year-to-date sales for all books in the titles table:
USE pubs
SELECT SUM(ytd_sales)
FROM titles
Here is the result set:
------------------
97446
(1 row(s) affected)
With this query, you can find the average price of all books if prices were doubled:
USE pubs
SELECT avg(price * 2)
FROM titles
Here is the result set:
------------------
29.53
(1 row(s) affected)
The table shows the syntax of the aggregate functions and their results (expression is almost always a column name).
| Aggregate function | Result |
|---|---|
| SUM([ALL | DISTINCT] expression) | Total of the values in the numeric expression |
| AVG([ALL | DISTINCT] expression) | Average of the values in the numeric expression |
| COUNT([ALL | DISTINCT] expression) | Number of values in the expression |
| 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.
The optional keyword DISTINCT can be used with SUM, AVG, and COUNT to eliminate duplicate values before an aggregate function is applied (the default is ALL).
SUM and AVG can be used only with numeric columns, for example int, smallint, tinyint, decimal, numeric, float, real, money, and smallmoney data types. MIN and MAX cannot be used with bit data types. Aggregate functions other than COUNT(*) cannot be used with text and image data types.
With these exceptions, aggregate functions can be used with any type of column. For example, in a character data type column, use MIN (minimum) to find the lowest value (the one closest to the beginning of the alphabet):
USE pubs
SELECT MIN(au_lname)
FROM authors
Here is the result set:
------------------
Bennet
(1 row(s) affected)
The result type returned by an aggregate function may have a larger precision than the inputs so that the result type is large enough to hold the aggregated result value. For example, the SUM or AVG functions return an int value when the data type of the inputs is smallint or tinyint. For more information about the data type returned by an aggregate function, see the topic for the function in Microsoft® SQL Server™ 2000 Transact-SQL Reference.
Note The output for statements, involving MIN or MAX on character columns, depends on the collation chosen during installation. For more information about the effects of different collations, see SQL Server Collation Fundamentals.
When aggregate functions are used in a select list, the select list can contain only:
- Aggregate functions.
- Grouping columns from a GROUP BY clause.
- An expression that returns the same value for every row in the result set, such as a constant.
For more information about generating aggregate values for result sets containing multiple rows, see Grouping Rows with GROUP BY.
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. 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 specified by the WHERE clause. 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.
This query returns the average advance and the sum of year-to-date sales for business books only:
USE pubs
SELECT AVG(advance), SUM(ytd_sales)
FROM titles
WHERE type = 'business'
Here is the result set:
--------- -------
6,281.25 30788
(1 row(s) affected)
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.