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

Modifiers

Notes:

¹ See Owner name


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.