Aggregate Functions
Index
General Aggregate Functions
AVG MAX MIN SUM EVERY |
ANY COUNT STDDEV_POP STDDEV_SAMP VAR_POP |
VAR_SAMP BIT_AND BIT_OR SELECTIVITY ENVELOPE |
Ordered Aggregate Functions
LISTAGG |
ARRAY_AGG |
Hypothetical Set Functions
RANK aggregate DENSE_RANK aggregate |
PERCENT_RANK aggregate CUME_DIST aggregate |
Inverse Distribution Functions
PERCENTILE_CONT PERCENTILE_DISC |
MEDIAN MODE |
JSON Aggregate Functions
JSON_OBJECTAGG |
JSON_ARRAYAGG |
Details
Click on the header to switch between railroad diagram and BNF.
General Aggregate Functions
AVG
The average (mean) value. If no rows are selected, the result is NULL
. Aggregates are only allowed in select statements. The returned value is of the same data type as the parameter.
Example:
AVG(X)
MAX
The highest value. If no rows are selected, the result is NULL
. Aggregates are only allowed in select statements. The returned value is of the same data type as the parameter.
Example:
MAX(NAME)
MIN
The lowest value. If no rows are selected, the result is NULL
. Aggregates are only allowed in select statements. The returned value is of the same data type as the parameter.
Example:
MIN(NAME)
SUM
The sum of all values. If no rows are selected, the result is NULL
. Aggregates are only allowed in select statements. The data type of the returned value depends on the parameter data type like this: BOOLEAN, TINYINT, SMALLINT, INT -> BIGINT, BIGINT -> DECIMAL, REAL -> DOUBLE
Example:
SUM(X)
EVERY
Returns true if all expressions are true. If no rows are selected, the result is NULL
. Aggregates are only allowed in select statements.
Example:
EVERY(ID>10)
ANY
Returns true if any expression is true. If no rows are selected, the result is NULL
. Aggregates are only allowed in select statements.
Note that if ANY
or SOME
aggregate function is placed on the right side of comparison operation and argument of this function is a subquery additional parentheses around aggregate function are required, otherwise it will be parsed as quantified comparison predicate.
Example:
ANY(NAME LIKE 'W%')
A = (ANY((SELECT B FROM T)))
COUNT
The count of all row, or of the non-null values. This method returns a long. If no rows are selected, the result is 0. Aggregates are only allowed in select statements.
Example:
COUNT(*)
STDDEV_POP
The population standard deviation. This method returns a double. If no rows are selected, the result is NULL
. Aggregates are only allowed in select statements.
Example:
STDDEV_POP(X)
STDDEV_SAMP
The sample standard deviation. This method returns a double. If no rows are selected, the result is NULL
. Aggregates are only allowed in select statements.
Example:
STDDEV(X)
VAR_POP
The population variance (square of the population standard deviation). This method returns a double. If no rows are selected, the result is NULL
. Aggregates are only allowed in select statements.
Example:
VAR_POP(X)
VAR_SAMP
The sample variance (square of the sample standard deviation). This method returns a double. If no rows are selected, the result is NULL
. Aggregates are only allowed in select statements.
Example:
VAR_SAMP(X)
BIT_AND
The bitwise AND
of all non-null values. If no rows are selected, the result is NULL
. Aggregates are only allowed in select statements.
Example:
BIT_AND(ID)
BIT_OR
The bitwise OR
of all non-null values. If no rows are selected, the result is NULL
. Aggregates are only allowed in select statements.
Example:
BIT_OR(ID)
SELECTIVITY
Estimates the selectivity (0-100) of a value. The value is defined as (100 * distinctCount / rowCount). The selectivity of 0 rows is 0 (unknown). Up to 10000 values are kept in memory. Aggregates are only allowed in select statements.
Example:
SELECT SELECTIVITY(FIRSTNAME), SELECTIVITY(NAME) FROM TEST WHERE ROWNUM()<20000
ENVELOPE
Returns the minimum bounding box that encloses all specified GEOMETRY
values. Only 2D coordinate plane is supported. NULL
values are ignored in the calculation. If no rows are selected, the result is NULL
. Aggregates are only allowed in select statements.
Example:
ENVELOPE(X)
Ordered Aggregate Functions
LISTAGG
LISTAGG ( |
| string |
|
| ) withinGroupSpecification |
| GROUP_CONCAT ( |
| string |
|
| ) |
|
|
Concatenates strings with a separator. Separator must be the same for all rows in the same group. The default separator is a ',' (without space). This method returns a string. NULL
values are ignored in the calculation, COALESCE
can be used to replace them. If no rows are selected, the result is NULL
. Aggregates are only allowed in select statements.
Example:
LISTAGG(NAME, ', ') WITHIN GROUP (ORDER BY ID)
LISTAGG(COALESCE(NAME, 'null'), ', ') WITHIN GROUP (ORDER BY ID)
LISTAGG(ID, ', ') WITHIN GROUP (ORDER BY ID) OVER (ORDER BY ID)
ARRAY_AGG
ARRAY_AGG ( |
| value |
| ) |
|
|
Aggregate the value into an array. This method returns an array. NULL
values are included in the array, FILTER
clause can be used to exclude them. If no rows are selected, the result is NULL
. If ORDER BY
is not specified order of values is not determined. When this aggregate is used with OVER
clause that contains ORDER BY
subclause it does not enforce exact order of values. This aggregate needs additional own ORDER BY
clause to make it deterministic. Aggregates are only allowed in select statements.
Example:
ARRAY_AGG(NAME ORDER BY ID)
ARRAY_AGG(NAME ORDER BY ID) FILTER (WHERE NAME IS NOT NULL)
ARRAY_AGG(ID ORDER BY ID) OVER (ORDER BY ID)
Hypothetical Set Functions
RANK aggregate
RANK ( value |
| ) |
withinGroupSpecification
|
|
Returns the rank of the hypothetical row in specified collection of rows. The rank of a row is the number of rows that precede this row plus 1. If two or more rows have the same values in ORDER BY
columns, these rows get the same rank from the first row with the same values. It means that gaps in ranks are possible.
See RANK
for a window function with the same name.
Example:
SELECT RANK(5) WITHIN GROUP (ORDER BY V) FROM TEST;
DENSE_RANK aggregate
DENSE_RANK ( value |
| ) |
withinGroupSpecification
|
|
Returns the dense rank of the hypothetical row in specified collection of rows. The rank of a row is the number of groups of rows with the same values in ORDER BY
columns that precede group with this row plus 1. If two or more rows have the same values in ORDER BY
columns, these rows get the same rank. Gaps in ranks are not possible.
See DENSE_RANK
for a window function with the same name.
Example:
SELECT DENSE_RANK(5) WITHIN GROUP (ORDER BY V) FROM TEST;
PERCENT_RANK aggregate
PERCENT_RANK ( value |
| ) |
withinGroupSpecification
|
|
Returns the relative rank of the hypothetical row in specified collection of rows. The relative rank is calculated as (RANK
- 1) / (NR
- 1), where RANK
is a rank of the row and NR
is a total number of rows in the collection including hypothetical row.
See PERCENT_RANK
for a window function with the same name.
Example:
SELECT PERCENT_RANK(5) WITHIN GROUP (ORDER BY V) FROM TEST;
CUME_DIST aggregate
CUME_DIST ( value |
| ) |
withinGroupSpecification
|
|
Returns the relative rank of the hypothetical row in specified collection of rows. The relative rank is calculated as NP
/ NR
where NP
is a number of rows that precede the current row or have the same values in ORDER BY
columns and NR
is a total number of rows in the collection including hypothetical row.
See CUME_DIST
for a window function with the same name.
Example:
SELECT CUME_DIST(5) WITHIN GROUP (ORDER BY V) FROM TEST;
Inverse Distribution Functions
PERCENTILE_CONT
PERCENTILE_CONT ( numeric ) WITHIN GROUP ( ORDER BY value |
| ) |
|
|
Return percentile of values from the group with interpolation. Interpolation is only supported for numeric, date-time, and interval data types. Argument must be between 0 and 1 inclusive. Argument must be the same for all rows in the same group. If argument is NULL
, the result is NULL
. NULL
values are ignored in the calculation. If no rows are selected, the result is NULL
. Aggregates are only allowed in select statements.
Example:
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY V)
PERCENTILE_DISC
PERCENTILE_DISC ( numeric ) WITHIN GROUP ( ORDER BY value |
| ) |
|
|
Return percentile of values from the group. Interpolation is not performed. Argument must be between 0 and 1 inclusive. Argument must be the same for all rows in the same group. If argument is NULL
, the result is NULL
. NULL
values are ignored in the calculation. If no rows are selected, the result is NULL
. Aggregates are only allowed in select statements.
Example:
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY V)
MEDIAN
The value separating the higher half of a values from the lower half. Returns the middle value or an interpolated value between two middle values if number of values is even. Interpolation is only supported for numeric, date-time, and interval data types. NULL
values are ignored in the calculation. If no rows are selected, the result is NULL
. Aggregates are only allowed in select statements.
Example:
MEDIAN(X)
MODE
| ||||||||||||||||||||||||
|
|
|
Returns the value that occurs with the greatest frequency. If there are multiple values with the same frequency only one value will be returned. In this situation value will be chosen based on optional ORDER BY
clause that should specify exactly the same expression as argument of this function. Use ascending order to get smallest value or descending order to get largest value from multiple values with the same frequency. If this clause is not specified the exact chosen value is not determined in this situation. NULL
values are ignored in the calculation. If no rows are selected, the result is NULL
. Aggregates are only allowed in select statements.
Example:
MODE(X)
MODE(X ORDER BY X)
MODE() WITHIN GROUP (ORDER BY X)
JSON Aggregate Functions
JSON_OBJECTAGG
JSON_OBJECTAGG ( |
| ||||||||||
|
|
|
)
|
|
Aggregates the keys with values into a JSON
object. If ABSENT ON NULL
is specified properties with NULL
value are not included in the object. If WITH UNIQUE KEYS
is specified the constructed object is checked for uniqueness of keys, nested objects, if any, are checked too. If no values are selected, the result is SQL NULL
value.
Example:
JSON_OBJECTAGG(NAME: VAL);
JSON_OBJECTAGG(KEY NAME VALUE VAL);
JSON_ARRAYAGG
JSON_ARRAYAGG ( expression |
| ) |
|
|
Aggregates the values into a JSON
array. If NULL ON NULL
is specified NULL
values are included in the array. If no values are selected, the result is SQL NULL
value.
Example:
JSON_ARRAYAGG(NUMBER)