Types of Aggregate Expressions

SQL statements can include built-in aggregates and user-defined aggregates. The built-in aggregates include all the aggregates shown in the syntax diagram in Aggregate Expressions except for the "User-Defined Aggregates" category. User-defined aggregates are any new aggregates that the user creates with the CREATE AGGREGATE statement.

Built-in Aggregates

Built-in aggregates are aggregate functions that are defined by the database server, such as AVG, SUM, and COUNT. These aggregates work only with built-in data types, such as INTEGER and FLOAT. You can extend these built-in aggregates to work with extended data types. To extend built-in aggregates, you must create UDRs that overload several binary operators.

After you overload the binary operators for a built-in aggregate, you can use that aggregate with an extended data type in an SQL statement. For example, if you have overloaded the plus operator for the SUM aggregate to work with a specified row type and assigned this row type to the complex column of the complex_tab table, you can apply the SUM aggregate to the complex column:

SELECT SUM(complex) FROM complex_tab

For more information on how to extend built-in aggregates, see IBM Informix User-Defined Routines and Data Types Developer's Guide. For information on how to invoke built-in aggregates, see the descriptions of individual built-in aggregates in the following pages.

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 and the discussion of user-defined aggregates in IBM Informix User-Defined Routines and Data Types Developer's Guide. For information on how to invoke user-defined aggregates, see User-Defined Aggregates (IDS).