Write an aggregate function

An aggregate is a function that returns one value for a group of queried rows. The aggregate function performs one iteration for each of the queried rows.

For each row, an aggregate iteration receives one column value (called the aggregate argument). The value that the aggregate returns to the SQL statement is called the aggregate result. For example, the following query calls the built-in SUM aggregate to determine the total cost of item numbers in order 1002: 
SELECT SUM(total_price) FROM items
WHERE order_num = 1002;

For this invocation of the SUM aggregate, each value of the total_price column that is passed into SUM is one aggregate argument. The total sum of all total_price values, which SUM returns to the SELECT, is the aggregate result.

The database server supports extensions of aggregates in the following ways:
  • Extensions of built-in aggregates
  • User-defined aggregates

You can write C user-defined functions to implement these aggregate extensions. For an overview of how to create aggregate functions and how to write them in an SPL routine, see the chapter on this topic in the Informix® User-Defined Routines and Data Types Developer's Guide. The following sections provide information specific to the creation of aggregate functions as C user-defined functions.