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 the following table.

Table 1. Permitted data types for built-in aggregates
Function Name Permitted Data Types
COUNT All types
MIN Numeric built-in data types
MAX Numeric built-in data types
AVG Numeric built-in data types
SUM Numeric built-in data types
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: