Aggregate (Group) Functions (SQL)

aggregate_functions ::= { COUNT | SUM | AVG | MIN | MAX } ([ ALL | DISTINCT ] expression) | COUNT(*)

Aggregation functions summarize data from queries on count (COUNT), sum (SUM), average (AVG), minimum (MIN) and maximum (MAX) over values set by the expression. The calculation is done either over all records in a query result (if the query does not contain a GROUP BY) or over each group from the GROUP BY. Any expression not containing another aggregate function may be an argument of these functions (the function value is counted from those argument values that are not NULL). The arguments must be numeric type for SUM and AVG functions. The MAX and MIN functions accept numeric or any of DATE, TIME, TIMESTAMP, BOOLEAN or character string types.

You can also use the * character in the COUNT function as an argument. In this case the function returns the count of all values, including those that equal NULL.

If you specify the DISTINCT keyword before a function argument, only different argument values are considered (for MAX and MIN functions this is irrelevant), if the ALL keyword is specified or omitted, all values are considered.

Aggregation functions can be used in the SELECT or HAVING clauses, but cannot be used in WHERE and GROUP BY clauses. They must replace the name of the result column in an ORDER BY clause, which can be specified using AS in a SELECT clause.

If these functions are used on an empty set of values, the COUNT function will return 0 and other functions return NULL.

Variations from Intermediate to Full

You can use DISTINCT with a expression in aggregate function arguments.
You can use references to columns that contain results of previous aggregate function in aggregate function arguments.

Variations from Intermediate to Entry

Aggregate functions CANNOT be used on variable-length columns (type CLOB and BLOB)

SQL properties in 602SQL defined by implementation

The result on the COUNT function is INT type. Other functions will return a value of the same type as the arguments with one exception. For AVG and SUM functions and SMALLINT or TINYINT type argument, the result will always be INT type. When counting the real average value of integer values requires a conversion of arguments to the REAL type: AVG(I + 0.0)

Usage examples

Count invoices:

SELECT COUNT(number) FROM invoices

Select all invoice with the minimum value:

SELECT *
FROM Invoices
WHERE amount = (SELECT MIN(amount) FROM Invoices)

Select all companies with invoices over 10000:

SELECT A.name
FROM Companies A
WHERE 10000 < (SELECT SUM(B.amount)
              FROM Invoices B
              WHERE A.number=B.company)

Select all companies that have at least one invoice over 5000:

SELECT COUNT (DISTINCT Companies.name) 
    FROM Invoices,Companies
    WHERE (amount > 5000)
      AND (Companies.number=Invoices.company)

CAST Function

Functions Defined by the SQL Standard