User-Defined Aggregates

A user-defined aggregate is an aggregate that you define to perform an aggregate computation that the database server does not provide. For example, you can create a user-defined aggregate named SUMSQ that returns the sum of the squared values of a specified column. User-defined aggregates can work with built-in data types or extended data types or both, depending on how you define the support functions for the user-defined aggregate.

To create a user-defined aggregate, use the CREATE AGGREGATE statement. In this statement you name the new aggregate and specify the support functions for the aggregate. Once you create the new aggregate and its support functions, you can use the aggregate in SQL statements. For example, if you created the SUMSQ aggregate and specified that it works with the FLOAT data type, you can apply the SUMSQ aggregate to a FLOAT column named digits in the test table:

SELECT SUMSQ(digits) FROM test;

For more information on how to create 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. For information on how to invoke user-defined aggregates, see User-Defined Aggregates.