Aggregate Functions

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 .
 
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 function 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 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