AGGREGATE Clause

13.2 AGGREGATE Clause

Identifies the function as an aggregate function, or one that evaluates a group of rows and returns a single row.

You can specify aggregate functions in the select list, HAVING clause, and ORDER BY clause.

When you specify a user-defined aggregate function in a query, you can treat it as an analytic function (one that operates on a query result set). To do so, use the OVER analytic_clause syntax available for SQL analytic functions.

The AGGREGATE clause can appear in the CREATE FUNCTION Statement.

Topics

Syntax

aggregate_clause ::=

Description of the illustration aggregate_clause.eps

Semantics

aggregate_clause

AGGREGATE USING

Specify the name of the implementation type of the function.

[schema.] implementation_type

The implementation type must be an ADT containing the implementation of the ODCIAggregate subprograms. If you do not specify schema, then the database assumes that the implementation type is in your schema.

Restriction on AGGREGATE USING

You cannot specify the aggregate_clause for a nested function.

If you specify this clause, then you can specify only one input argument for the function.

Examples

Example 12-33, "Pipelined Table Function as Aggregate Function"