CREATE AGGREGATE

Use the CREATE AGGREGATE statement to create a new aggregate function and register it in the sysaggregates system catalog table. User-defined aggregates extend the functionality of the database server by performing aggregate computations that the user implements.

Only Dynamic Server supports this statement, which is an extension to the ANSI/ISO standard for SQL.

Syntax

>>-CREATE AGGREGATE--+------------------------+--aggregate------>
                     |                (1)     |
                     '-| Owner Name |-------.-'
 
            .-,-------------.
            V               |
>--WITH--(----| Modifiers |-+--)-------------------------------><
 
Modifiers:
 
|--+-INIT=init_func-------+-------------------------------------|
   +-ITER=iter_func-------+
   +-COMBINE=comb_func----+
   '-+-FINAL=final_func-+-'
     '-HANDLESNULLS-----'

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	Database Object Name
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	Database Object Name
init_func	Function that initializes the data structures required for the aggregate computation	Must be able to handle NULL arguments	Database Object Name
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	Database Object Name

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 have 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 have to exist at the time 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.