Subset of Expressions Valid in an Aggregate Expression

As indicated in the diagrams for Aggregate Expressions and User-Defined Aggregates (IDS), not all expressions are available when you use an aggregate expression. The argument of an aggregate function, for example, cannot itself contain an aggregate function. You cannot use aggregate functions in the following contexts:

• In a WHERE clause, unless it is contained in a subquery, or unless the aggregate is on a correlated column from a parent query and the WHERE clause is in a subquery within a HAVING clause
• As an argument to an aggregate function

  The following nested aggregate expression is invalid:

  MAX (AVG (order_num)) 

• On a BYTE or TEXT column

You cannot use a column that is a collection data type as an argument to the following aggregate functions:

• AVG
• SUM
• MIN
• MAX

Expression or column arguments to built-in aggregates (except for COUNT, MAX, MIN, and RANGE) must return numeric or INTERVAL data types, but RANGE also accepts DATE and DATETIME arguments.

For SUM and AVG, you cannot use the difference between two DATE values directly as the argument to an aggregate, but you can use DATE differences as operands within arithmetic expression arguments. For example:

SELECT . . .  AVG(ship_date - order_date)

returns error -1201, but the following equivalent expression is valid:

SELECT . . .  AVG((ship_date - order_date)*1)