Subset of Expressions Valid in an Aggregate Expression

As indicated in the diagrams for Aggregate Expressions and User-Defined Aggregates, 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, but with these two exceptions:
  • unless the aggregate is specified in the Projection clause of a subquery within the WHERE clause,
  • 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 not valid:

  MAX (AVG (order_num)) 

• On a column of any of the following data types:
  • Large object (BLOB, BYTE, CLOB, TEXT)
  • Collection data types (LIST, MULTISET, SET)
  • ROW data types (named or unnamed)
  • OPAQUE data types (except with user-defined aggregate functions that support opaque types).

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

• AVG
• SUM
• MIN
• MAX

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);

The following query fragment uses valid syntax to declare aliases for two column expressions:

SELECT . . . 
   SUM(orders.ship_charge) as o2,
   COUNT(DISTINCT 
         CASE WHEN orders.backlog MATCHES 'n' 
              THEN orders.order_num END ) AS o3, 
   . . .

Here the argument to SUM is a MONEY(6) column value, and the COUNT DISTINCT aggregate takes a CASE expression as its argument.