SQL Aggregate functions

Aggregate functions operate against a collection of values, but return a single, summarizing value. Aggregate functions return a single value based upon a set of other values. If used among many other expressions in the item list of a SELECT statement, the SELECT must have a GROUP BY clause. No GROUP BY clause is required if the aggregate function is the only value retrieved by the SELECT statement. The supported aggregate functions and their syntax are listed in Table 4-1.
Table 4-1: SQL99 Aggregate Functions
---------------------------------------------------------------------------------
Function |Usage
---------------------------------------------------------------------------------
AVG(expression) |Computes the average value of a column by the expression
---------------------------------------------------------------------------------
COUNT(expression)|Counts the rows defined by the expression
---------------------------------------------------------------------------------
COUNT(*) |Counts all rows in the specified table or view
---------------------------------------------------------------------------------
MIN(expression) |Finds the minimum value in a column by the expression
---------------------------------------------------------------------------------
MAX(expression) |Finds the maximum value in a column by the expression
---------------------------------------------------------------------------------
SUM(expression) |Computes the sum of column values by the expression
---------------------------------------------------------------------------------

Technically speaking, ANY, EVERY, and SOME are considered aggregate functions. However, they have been discussed as range search criteria since they are most often used that way. Refer to the SELECT . . . WHERE topic in the previous chapter for more information on these functions.
The number of values processed by an aggregate varies depending on the number of rows queried from the table. This behavior makes aggregate functions different from scalar functions, which require a fixed number and fixed type of parameters.
The general syntax of an aggregate function is:

aggregate_function_name ( [ALL | DISTINCT] expression )

The aggregate function name may be AVG, COUNT, MAX, MIN, or SUM. The ALL clause, which is the default behavior and does not actually need to be specified, evaluates all rows when aggregating the value of the function. The DISTINCT clause uses only distinct values when evaluating the function.

AVG and SUM
The AVG function computes the average of values in a column or an expression. SUM computes the sum. Both functions work with numeric values and ignore NULL values. They also can be used to compute the average or sum of all distinct values of a column or expression.
AVG and SUM are supported by Microsoft SQL Server, MySQL, Oracle, and PostgreSQL.

Example
The following query computes average year-to-date sales for each type of book:

SELECT type, AVG( ytd_sales ) AS "average_ytd_sales"
FROM titles
GROUP BY type;

This query returns the sum of year-to-date sales for each type of book:

SELECT type, SUM( ytd_sales )
FROM titles
GROUP BY type;

COUNT
The COUNT function has three variations. COUNT(*) counts all the rows in the target table whether they include nulls or not. COUNT(expression) computes the number of rows with non-NULL values in a specific column or expression. COUNT(DISTINCT expression) computes the number of distinct non-NULL values in a column or expression.

Examples
This query counts all rows in a table:

SELECT COUNT(*) FROM publishers;

The following query finds the number of different countries where publishers are located:

SELECT COUNT(DISTINCT country) "Count of Countries"
FROM publishers

MIN and MAX
MIN(expression) and MAX(expression) find the minimum and maximum value (string, datetime, or numeric) in a set of rows. DISTINCT or ALL may be used with these functions, but they do not affect the result.
MIN and MAX are supported by Microsoft SQL Server, MySQL, Oracle, and PostgreSQL.
MySQL also supports the functions LEAST() and GREATEST(), providing the same capabilities.

Examples
The following query finds the best and worst sales for any title on record:

SELECT 'MIN' = MIN(ytd_sales), 'MAX' = MAX(ytd_sales)
FROM titles;

Aggregate functions are used often in the HAVING clause of queries with GROUP BY. The following query selects all categories (types) of books that have an average price for all books in the category higher than $15.00:

SELECT type 'Category', AVG( price ) 'Average Price'
FROM titles
GROUP BY type
HAVING AVG(price) > 15