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 4, "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 the empty set,
then the aggregate functions are not applied, the result of the query is the
empty set.
If a GROUP BY clause is not specified in a query and the intermediate
result of the FROM, WHERE, and HAVING clauses is the empty set, then the aggregate
functions are applied to the empty set. For example, the result of the following
SELECT statement is applied to the empty set 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.
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.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.