Aggregates (set functions)

This section describes aggregates (also described as set functions in ANSI SQL and as column functions in some database literature).

Aggregates provide a means of evaluating an expression over a set of rows. Whereas the other built-in functions operate on a single expression, aggregates operate on a set of values and reduce them to a single scalar value. Built-in aggregates can calculate the minimum, maximum, sum, count, and average of an expression over a set of values as well as count rows.

In addition to the built-in aggregates, Derby allows you to create custom aggregate operators, called user-defined aggregates (UDAs). For information on creating and removing UDAs, see CREATE DERBY AGGREGATE statement and DROP DERBY AGGREGATE statement. See GRANT statement and REVOKE statement for information on usage privileges for UDAs.

For information on writing the Java classes that implement UDAs, see "Programming user-defined aggregates" in the Derby Developer's Guide.

The built-in aggregates can operate on expressions that evaluate to the data types shown in the following table.

Table 1. Permitted data types for built-in aggregates
Function Name	Permitted Data Types
COUNT	All types
MIN	Data types that can be indexed
MAX	Data types that can be indexed
AVG	Numeric built-in data types
SUM	Numeric built-in data types

Aggregates are permitted only in the following:

A selectItem in a selectExpression.
A HAVING clause.
An ORDER BY clause (using an alias name) if the aggregate appears in the result of the relevant query block. That is, an alias for an aggregate is permitted in an ORDER BY clause if and only if the aggregate appears in a selectItem in a selectExpression.

All expressions in selectItems in the selectExpression must be either aggregates or grouped columns (see GROUP BY clause). (The same is true if there is a HAVING clause without a GROUP BY clause.) This is because the ResultSet of a selectExpression must be either a scalar (single value) or a vector (multiple values), but not a mixture of both. (Aggregates evaluate to a scalar value, and the reference to a column can evaluate to a vector.) For example, the following query mixes scalar and vector values and thus is not valid:

-- not valid
SELECT MIN(flying_time), flight_id
FROM Flights

Aggregates are not allowed on outer references (correlations). This means that if a subquery contains an aggregate, that aggregate cannot evaluate an expression that includes a reference to a column in the outer query block. For example, the following query is not valid because SUM operates on a column from the outer query:

SELECT c1
FROM t1
GROUP BY c1
HAVING c2 >
    (SELECT t2.x
    FROM t2
    WHERE t2.y = SUM(t1.c3))

A cursor declared on a ResultSet that includes an aggregate in the outer query block is not updatable.