CREATE AGGREGATE statement

Use the CREATE AGGREGATE statement to create a new aggregate function and register it in the sysaggregates system catalog table.

User-defined aggregates (UDA) extend the functionality of the database server by performing aggregate calculations that the user implements.

This statement is an extension to the ANSI/ISO standard for SQL.

Syntax

(explicit id creag002) creag002 CREATE AGGREGATE [IF NOT EXISTS] [ <Owner Name> []. ] aggregate WITH ( <Modifiers> )

Modifiers

{ INIT=init_func | ITER=iter_func | COMBINE=comb_func | { FINAL=final_func | HANDLESNULLS } }
Element Description Restrictions Syntax
aggregate Name of the new aggregate Must be unique among names of built-in aggregates and UDRs Identifier
comb_func Function that merges one partial result into the other and returns the updated partial result Must specify the combined function both for parallel queries and for sequential queries Identifier
final_func Function that converts a partial result into the result type If this is omitted, then the returned value is the final result of iter_func Identifier
init_func Function that initializes the data structures required for the aggregate computation Must be able to handle NULL arguments Identifier
iter_func Function that merges a single value with a partial result and returns updated partial result Must specify an iterator function. If init_func is omitted, iter_func must be able to handle NULL arguments Identifier

Usage

You can specify the INIT, ITER, COMBINE, FINAL, and HANDLESNULLS modifiers in any order.
Important: You must specify the ITER and COMBINE modifiers in a CREATE AGGREGATE statement. You do not need to specify the INIT, FINAL, and HANDLESNULLS modifiers in a CREATE AGGREGATE statement.

The ITER, COMBINE, FINAL, and INIT modifiers specify the support functions for a user-defined aggregate. These support functions do not need to exist at the time when you create the user-defined aggregate.

If you omit the HANDLESNULLS modifier, rows with NULL aggregate argument values do not contribute to the aggregate computation. If you include the HANDLESNULLS modifier, you must define all the support functions to handle NULL values as well.

Important: A SELECT statement can include no more than one UDA expression whose first argument is the DISTINCT or UNIQUE keyword (rather than the ALL keyword, or no keyword). In a query that includes subqueries, however, you can specify either zero or one DISTINCT or UNIQUE user-defined aggregate expression at each level of the query. Built-in aggregates are not subject to this restriction.

If you include the optional IF NOT EXISTS keywords, the database server takes no action (rather than sending an exception to the application) if an aggregate of the specified name is already registered in the current database.

1 See Owner name