Aggregate functions take sets of values from a group produced by an explicit or implicit
GROUP BY and return a single scalar value computed from the group.
The Data Virtuality Server supports the following aggregate functions:
|Counts number of values (including nulls and duplicates) in a group|
|Counts number of values (excluding nulls) in a group|
|Sum of values (excluding nulls) in a group|
|Average of values (excluding nulls) in a group|
|Minimum value in a group (excluding null)|
|Maximum value in a group (excluding null)|
|Biased variance (excluding null) logically equals |
|Sample variance (excluding null) logically equals |
|Standard deviation (excluding null) logically equals |
|Sample standard deviation (excluding null) logically equals |
CSV text aggregation of all expressions in each row of a group.
XML concatenation of all xml expressions in a group (excluding null). The
Creates a JSON array result as a Clob including null value. The
Creates a lob result from the concatenation of x using the delimiter
Creates an array with a base type matching the expression x.
|User-defined aggregate function|
Some aggregate functions may contain the keyword '
DISTINCT' before the expression, indicating that duplicate expression values should be ignored.
DISTINCT is not allowed in
COUNT(*) and is not meaningful in
MAX (the result would be unchanged), but it can be used in
Some other things to keep in mind when working with aggregate functions:
- They cannot be used in
GROUP BY, or
WHEREclauses without an intervening query expression;
- They cannot be nested within another aggregate function without an intervening query expression;
- They may be nested inside other functions;
Any aggregate function may take an optional
FILTERclause of the following form:
FILTER ( WHERE condition )CODE
The condition may be any boolean value expression that does not contain a subquery or a correlated variable. The filter will logically be evaluated for each row before the grouping operation. If
FALSE, the aggregate function will not accumulate a value for the given row;
- User-defined aggregate functions need
ALLspecified if no other aggregate-specific constructs are used to distinguish the function as an aggregate rather than a normal function. For more information on aggregates, see the sections on