SQL-J Language Reference
Page 10 of 121

CREATE AGGREGATE statement

The CREATE AGGREGATE statement creates a user-defined aggregate. An aggregate, also known as a set function or a column function, provides a means for evaluating an expression over a set of rows. Cloudscape provides a number of built-in aggregates, such as MAX, MIN, COUNT, and the like. (For more information, see Aggregates (Set Functions).) With CREATE AGGREGATE, you can define your own aggregate functions. For example, whereas MAX gives you the maximum value in a column, you could define an aggregate called MAXBUTONE that would return the second highest value in a column, provided that you created a Java class to implement the aggregation. Another example is standard deviation; with the appropriate Java class to back it up, you could create an aggregate that operates on a set of doubles and returns the standard deviation.

When you create an aggregate, you specify a Java class that implements COM.cloudscape.aggregates.AggregateDefinition.

For information about creating such classes, see Programming User-Defined Aggregates in the Cloudscape Developer's Guide.

Syntax

CREATE AGGREGATE AggregateName FOR
    { JavaClassName | ClassAlias }

You cannot create an aggregate with the same name as a method alias.

You cannot create an aggregate with the same name as a built-in aggregate.

User-defined aggregates are not permitted in check constraints.

CREATE AGGREGATE Examples

CREATE AGGREGATE MAXBUTONE FOR
    JBMSTours.aggregates.MaxButOneDef

CREATE AGGREGATE STDEV FOR
    JBMSTours.aggregates.StandardDeviation

SELECT MAX(DISTINCT miles) AS Highest, MAXBUTONE(DISTINCT miles) AS SecondHighest
FROM Flights

SELECT AVG(flying_time), STDEV(flying_time)
FROM Flights