An aggregate function takes a set of values (like a column of data) and returns a single value result from the set of values.
The following information applies to all aggregate functions other than COUNT(*) and COUNT_BIG(*).
- The argument of an aggregate function is a set of values derived from an expression. The expression may include columns but cannot include another aggregate function. The scope of the set is a group or an intermediate result table as explained in Chapter 6, "Queries".
- If a GROUP BY clause is specified in a query and the intermediate result of the FROM, WHERE, GROUP BY, and HAVING clauses is an empty result table, then the aggregate functions are not applied and the result of the query is an empty table.
- If a GROUP BY clause is not specified in a query and the intermediate
result of the FROM, WHERE, and HAVING clauses is an
empty result table, then the aggregate functions are applied
to the empty result table. For example, the result
of the following SELECT statement is applied to an
empty result table because department D01 has no employees:
SELECT COUNT(DISTINCT JOB) FROM EMPLOYEE WHERE WORKDEPT = 'D01'
- The keyword DISTINCT is not considered an argument of the function,
but rather a specification of an operation that is performed before
the function is applied. If DISTINCT is specified, redundant duplicate
values are eliminated. If ALL is implicitly or explicitly specified,
redundant duplicate values are not eliminated.
When interpreting the DISTINCT clause for decimal floating-point values that are numerically equal, the number of significant digits in the value is not considered. For example, the decimal floating-point number 123.00 is not distinct from the decimal floating-point number 123. The representation of the number returned from the query will be any one of the representations encountered (for example, either 123.00 or 123).
- An aggregate function can be used in a WHERE clause only if that clause is part of a subquery of a HAVING clause and the column name specified in the expression is a correlated reference to a group. If the expression includes more than one column name, each column name must be a correlated reference to the same group.