Aggregates (set functions)

This section describes aggregates (also described as set functions in ANSI SQL-92 and as column functions in some database literature). They 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.

The built-in aggregates can operate on the data types shown in Permitted Data Types for Built-in Aggregates.
Table 1. Permitted Data Types for Built-in Aggregates
Function Name All Types Numeric Built-in Data Types
COUNT X X
MIN ?? X
MAX ?? X
AVG ?? X
SUM ?? X
Aggregates are permitted only in the following:
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.

Derby supports the following aggregates: