Aggregate Functions
Index
General Aggregate Functions
|
AVG MAX MIN SUM EVERY ANY COUNT |
STDDEV_POP STDDEV_SAMP VAR_POP VAR_SAMP ANY_VALUE BIT_AND_AGG BIT_OR_AGG |
BIT_XOR_AGG BIT_NAND_AGG BIT_NOR_AGG BIT_XNOR_AGG ENVELOPE |
Binary Set Functions
|
COVAR_POP COVAR_SAMP CORR REGR_SLOPE |
REGR_INTERCEPT REGR_COUNT REGR_R2 REGR_AVGX |
REGR_AVGY REGR_SXX REGR_SYY REGR_SXY |
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 of the function to switch between railroad diagram and BNF.
Non-standard syntax is marked in green. Compatibility-only non-standard syntax is marked in red, don't use it unless you need it for compatibility with other databases or old versions of H2.
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 data type of result is DOUBLE PRECISION for TINYINT, SMALLINT, INTEGER, and REAL arguments, NUMERIC with additional 10 decimal digits of precision and scale for BIGINT and NUMERIC arguments; DECFLOAT with additional 10 decimal digits of precision for DOUBLE PRECISION and DECFLOAT arguments; INTERVAL with the same leading field precision, all additional smaller datetime units in interval qualifier, and the maximum scale for INTERVAL arguments.
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 result is BIGINT for BOOLEAN, TINYINT, SMALLINT, and INTEGER arguments; NUMERIC with additional 10 decimal digits of precision for BIGINT and NUMERIC arguments; DOUBLE PRECISION for REAL arguments, DECFLOAT with additional 10 decimal digits of precision for DOUBLE PRECISION and DECFLOAT arguments; INTERVAL with maximum precision and the same interval qualifier and scale for INTERVAL arguments.
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 or distinct predicate and argument of this function is a subquery additional parentheses around aggregate function are required, otherwise it will be parsed as quantified 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 less than two 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 less than two rows are selected, the result is NULL. Aggregates are only allowed in select statements.
Example:
VAR_SAMP(X)
ANY_VALUE
Returns any non-NULL value from aggregated values. If no rows are selected, the result is NULL. This function uses the same pseudo random generator as RAND() function.
If DISTINCT is specified, each distinct value will be returned with approximately the same probability as other distinct values. If it isn't specified, more frequent values will be returned with higher probability than less frequent.
Aggregates are only allowed in select statements.
Example:
ANY_VALUE(X)
BIT_AND_AGG
The bitwise AND of all non-null values. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements.
For non-aggregate function see BITAND.
Example:
BIT_AND_AGG(X)
BIT_OR_AGG
The bitwise OR of all non-null values. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements.
For non-aggregate function see BITOR.
Example:
BIT_OR_AGG(X)
BIT_XOR_AGG
The bitwise XOR of all non-null values. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements.
For non-aggregate function see BITXOR.
Example:
BIT_XOR_AGG(X)
BIT_NAND_AGG
The bitwise NAND of all non-null values. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements.
For non-aggregate function see BITNAND.
Example:
BIT_NAND_AGG(X)
BIT_NOR_AGG
The bitwise NOR of all non-null values. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements.
For non-aggregate function see BITNOR.
Example:
BIT_NOR_AGG(X)
BIT_XNOR_AGG
The bitwise XNOR of all non-null values. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements.
For non-aggregate function see BITXNOR.
Example:
BIT_XNOR_AGG(X)
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)
Binary Set Functions
COVAR_POP
COVAR_POP ( dependentExpression , independentExpression ) |
|
|
The population covariance. This method returns a double. Rows in which either argument is NULL are ignored in the calculation. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements.
Example:
COVAR_POP(Y, X)
COVAR_SAMP
COVAR_SAMP ( dependentExpression , independentExpression ) |
|
|
The sample covariance. This method returns a double. Rows in which either argument is NULL are ignored in the calculation. If less than two rows are selected, the result is NULL. Aggregates are only allowed in select statements.
Example:
COVAR_SAMP(Y, X)
CORR
CORR ( dependentExpression , independentExpression ) |
|
|
Pearson's correlation coefficient. This method returns a double. Rows in which either argument is NULL are ignored in the calculation. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements.
Example:
CORR(Y, X)
REGR_SLOPE
REGR_SLOPE ( dependentExpression , independentExpression ) |
|
|
The slope of the line. This method returns a double. Rows in which either argument is NULL are ignored in the calculation. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements.
Example:
REGR_SLOPE(Y, X)
REGR_INTERCEPT
REGR_INTERCEPT ( dependentExpression , independentExpression ) |
|
|
The y-intercept of the regression line. This method returns a double. Rows in which either argument is NULL are ignored in the calculation. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements.
Example:
REGR_INTERCEPT(Y, X)
REGR_COUNT
REGR_COUNT ( dependentExpression , independentExpression ) |
|
|
Returns the number of rows in the group. This method returns a long. Rows in which either argument is NULL are ignored in the calculation. If no rows are selected, the result is 0. Aggregates are only allowed in select statements.
Example:
REGR_COUNT(Y, X)
REGR_R2
REGR_R2 ( dependentExpression , independentExpression ) |
|
|
The coefficient of determination. This method returns a double. Rows in which either argument is NULL are ignored in the calculation. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements.
Example:
REGR_R2(Y, X)
REGR_AVGX
REGR_AVGX ( dependentExpression , independentExpression ) |
|
|
The average (mean) value of dependent expression. Rows in which either argument is NULL are ignored in the calculation. If no rows are selected, the result is NULL. For details about the data type see AVG. Aggregates are only allowed in select statements.
Example:
REGR_AVGX(Y, X)
REGR_AVGY
REGR_AVGY ( dependentExpression , independentExpression ) |
|
|
The average (mean) value of independent expression. Rows in which either argument is NULL are ignored in the calculation. If no rows are selected, the result is NULL. For details about the data type see AVG. Aggregates are only allowed in select statements.
Example:
REGR_AVGY(Y, X)
REGR_SXX
REGR_SXX ( dependentExpression , independentExpression ) |
|
|
The the sum of squares of independent expression. Rows in which either argument is NULL are ignored in the calculation. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements.
Example:
REGR_SXX(Y, X)
REGR_SYY
REGR_SYY ( dependentExpression , independentExpression ) |
|
|
The the sum of squares of dependent expression. Rows in which either argument is NULL are ignored in the calculation. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements.
Example:
REGR_SYY(Y, X)
REGR_SXY
REGR_SXY ( dependentExpression , independentExpression ) |
|
|
The the sum of products independent expression times dependent expression. Rows in which either argument is NULL are ignored in the calculation. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements.
Example:
REGR_SXY(Y, X)
Ordered Aggregate Functions
LISTAGG
LISTAGG ( |
| string |
|
| ) |
withinGroupSpecification
|
|
Concatenates strings with a separator. 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.
If ON OVERFLOW TRUNCATE is specified, values that don't fit into returned string are truncated and replaced with filter string placeholder ('...' by default) and count of truncated elements in parentheses. If WITHOUT COUNT is specified, count of truncated elements is not appended.
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)
LISTAGG(ID, ';' ON OVERFLOW TRUNCATE 'etc' WITHOUT COUNT) WITHIN GROUP (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
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
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
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
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 sortSpecification ) |
|
|
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 sortSpecification ) |
|
|
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() 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)