User-Defined Aggregates

You can create your own aggregate expressions with the CREATE AGGREGATE statement and then invoke these aggregates wherever you can invoke the built-in aggregates.

The following diagram shows the syntax for invoking a user-defined aggregate.

Notes:

¹ See Subset of Expressions Valid in an Aggregate Expression


Element	Description	Restrictions	Syntax
aggregate	Name of the user-defined aggregate to invoke	The aggregate and the support functions defined for aggregate must exist	Identifier
column	Name of a column within table	Must exist and have a numeric data type	Quoted String
setup_expr	Set-up expression that customizes aggregate for a specific invocation	Cannot be a lone host variable. Any columns referenced in setup_expr must be in the GROUP BY clause of the query	Expression
synonym, table, view	Synonym, table, or view in which column occurs	The synonym and the table or view to which it points must exist	Identifier

Use the DISTINCT or UNIQUE keywords to specify that the user-defined aggregate is to be applied only to unique values in the named column or expression. Use the ALL keyword to specify that the aggregate is to be applied to all values in the named column or expression.

If you omit the DISTINCT, UNIQUE, and ALL keywords, ALL is the default. For further information on the DISTINCT, UNIQUE, and ALL keywords, see Including or excluding duplicates in the result set.

When you specify a setup expression, this value is passed to the INIT support function that was defined for the user-defined aggregate in the CREATE AGGREGATE statement.

In the following example, you apply the user-defined aggregate named my_avg to all values of the quantity column in the items table:

SELECT my_avg(quantity) FROM items

In the following example, you apply the user-defined aggregate named my_sum to unique values of the quantity column in the items table. You also supply the value 5 as a setup expression. This value might specify that the initial value of the sum that my_avg will compute is 5.

SELECT my_sum(DISTINCT quantity, 5) FROM items

In the following example, you apply the user-defined aggregate named my_max to all values of the quantity column in the remote items table:

SELECT my_max(remote.quantity) FROM rdb@rserv:items remote

If the my_max aggregate is defined as EXECUTEANYWHERE, then the distributed query can be pushed to the remote database server, rserv, for execution. If the my_max aggregate is not defined as EXECUTEANYWHERE, then the distributed query scans the remote items table and computes the my_max aggregate on the local database server.

You cannot qualify a user-defined aggregate with the name of a remote database server, as the following example shows. In this case, the database server returns an error:

SELECT rdb@rserv:my_max(remote.quantity) 
   FROM rdb@rserv:items remote

For further information on user-defined aggregates, see CREATE AGGREGATE statement and the discussion of user-defined aggregates in HCL OneDB™ User-Defined Routines and Data Types Developer's Guide.