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.
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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(value) [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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(value) [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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( [ DISTINCT|ALL ] { numeric | interval | { boolean } } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
FILTER ( WHERE expression )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| {BOOL_AND}}(boolean)
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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|SOME| {BOOL_OR}}(boolean)
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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( { * | { [ DISTINCT|ALL ] expression } } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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( [ DISTINCT|ALL ] numeric ) [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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( [ DISTINCT|ALL ] numeric ) [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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( [ DISTINCT|ALL ] numeric ) [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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( [ DISTINCT|ALL ] numeric ) [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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( [ DISTINCT|ALL ] value ) [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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}|{BIT_AND}}(expression)
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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}|{BIT_OR}}(expression)
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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( [ DISTINCT|ALL ] expression)
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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(expression)
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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(expression)
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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( [ DISTINCT|ALL ] expression)
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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( value )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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)
COVAR_POP(dependentExpression, independentExpression) [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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(dependentExpression, independentExpression) [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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(dependentExpression, independentExpression) [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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(dependentExpression, independentExpression) [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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(dependentExpression, independentExpression) [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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(dependentExpression, independentExpression) [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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(dependentExpression, independentExpression) [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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(dependentExpression, independentExpression) [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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(dependentExpression, independentExpression) [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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(dependentExpression, independentExpression) [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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(dependentExpression, independentExpression) [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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(dependentExpression, independentExpression) [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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)
LISTAGG ( [ DISTINCT|ALL ] string [, separatorString] [ ON OVERFLOW { ERROR | TRUNCATE [ filterString ] { WITH | WITHOUT } COUNT } ] ) withinGroupSpecification [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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 ( [ DISTINCT|ALL ] value [ ORDER BY sortSpecificationList ] ) [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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)
RANK(value [,...]) withinGroupSpecification [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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(value [,...]) withinGroupSpecification [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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(value [,...]) withinGroupSpecification [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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(value [,...]) withinGroupSpecification [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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;
PERCENTILE_CONT(numeric) WITHIN GROUP (ORDER BY sortSpecification) [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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(numeric) WITHIN GROUP (ORDER BY sortSpecification) [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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( [ DISTINCT|ALL ] value )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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() WITHIN GROUP (ORDER BY sortSpecification) } | { MODE( value [ ORDER BY sortSpecification ] ) } [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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_OBJECTAGG(
{[KEY] string VALUE value} | {string : value}
[ { NULL | ABSENT } ON NULL ]
[ { WITH | WITHOUT } UNIQUE KEYS ]
)
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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( [ DISTINCT|ALL ] expression [ ORDER BY sortSpecificationList ] [ { NULL | ABSENT } ON NULL ] ) [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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)