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 count rows as well as calculate the minimum, maximum, sum, count, average,
variance, and standard deviation of an expression over a set of values.
In addition to the built-in aggregates,
allows you to create
custom aggregate operators, called user-defined aggregates (UDAs). For
information on creating and removing UDAs, see
and
. See
and
for information on usage
privileges for UDAs.
For information on writing the Java classes that implement UDAs, see
"Programming user-defined aggregates" in the
.
The built-in aggregates can operate on expressions that evaluate to
the data types shown in the following table.
Permitted data types for built-in aggregates
This table lists the data types that are valid for each of the built-in aggregates.
Function Name
Permitted Data Types
AVG
Numeric built-in data types
COUNT
All types
MAX
Data types that can be indexed
MIN
Data types that can be indexed
STDDEV_POP
Numeric built-in data types
STDDEV_SAMP
Numeric built-in data types
SUM
Numeric built-in data types
VAR_POP
Numeric built-in data types
VAR_SAMP
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 ).
(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.