Aggregate functions return the average, count, maximum value, minimum value or sum of a set of values or the number of rows in a derived table.
aggregate_function ::= {
operator ([ ALL | DISTINCT ]
expression ) | COUNT(*) }
operator ::= {AVG | COUNT | MAX | MIN | SUM }
expression
Expression determining the values in the set (see section "Expression").
The expression for each aggregate function except for COUNT(*) can have a certain data type. The permitted data type(s) for each function is specified in the function description.
The following restrictions apply to expression:
expression cannot include any multiple columns.
expression cannot include any aggregate functions.
expression cannot include any subqueries.
If a column name in expression specifies a column of a higher-level query expression (external reference), expression may only include this column name.
In this case, the aggregate function must satisfy one of the following conditions:
The aggregate function is included in a SELECT list.
The aggregate function is included in a subquery of a HAVING clause. The column name must indicate a column of the SELECT expression that contains a HAVING clause.
The aggregate functions MIN() and MAX() reference the set of all values in a column in a table. They differ in this way from a CASE expression with MIN / MAX (see "CASE expression with MIN / MAX"), which references different expressions.
Calculating aggregate functions
In all the aggregate functions except COUNT(*), the expression specified as the function argument determines the set of values used in the aggregate function.
If the SELECT expression or SELECT statement in which the aggregate function occurs does not include a GROUP BY clause, the argument expression is used on all the rows in the table (or the rows that satisfy the WHERE clause) referenced by the column specifications in the argument expression. If the argument expression does not contain a column specification, the argument expression is used on all the rows in the table of the SELECT expression. The result is a single-column table.
If this table contains NULL values, these are removed before the aggregate function is performed. A warning is issued.
If DISTINCT is specified in the aggregate function, only unique values are taken into account, i.e. if a value occurs more than once in a table, the duplicates are removed before the aggregate function is performed.
The aggregate function is then used on the remaining values of the single-column table and returns exactly one value.
If the corresponding SELECT expression (or SELECT statement) includes a GROUP BY clause, the aggregate function is calculated as described for each group separately and returns exactly one value per group.
Examples
Without GROUP BY: The following expression calculates the sum of the trebled price of the items from the ITEMS table:
SELECT SUM (3*price) FROM items
In order to calculate the expression, the argument expression 3*price
is used on all the rows of the ITEMS table. This returns the following derived column:
2101.50 690,00 450.00 450.00 120.00 120.00 180.00 15.00 15.00 30.00 3.00 3.30 2.25
The sum of the values is 41880.05.
With GROUP BY: The following expression calculates the total stock per location from the WAREHOUSE table.
SELECT location, SUM (stock) FROM warehouse GROUP BY location
In order to calculate the expression the stock per location is grouped together first:
location stock Main warehouse 2 1 10 10 3 3 1 15 8 6 11 120 248 Parts warehouse 9 6 3 200 180 47
Subsequently, the stock is added together for each warehouse.
location Main warehouse 438 Parts warehouse 445