H2 database logo   ▲

Home
Download
Cheat Sheet

Documentation
Quickstart
Installation
Tutorial
Features
Performance
Advanced

Reference
Commands
Functions
• Aggregate • Window

Data Types
SQL Grammar
System Tables
Javadoc
PDF (1.5 MB)

Support
FAQ
Error Analyzer
Google Group (English)
Google Group (Japanese)
Google Group (Chinese)

Appendix
History & Roadmap
License
Build
Links
MVStore
Architecture

 

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

AVG ( [ DISTINCT|ALL ] { numeric } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
AVG (
 
DISTINCT
ALL
numeric )

 
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 returned value is of the same data type as the parameter.

Example:

AVG(X)

MAX

MAX(value)
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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

MIN(value)
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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

SUM( [ DISTINCT|ALL ] { numeric } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
SUM (
 
DISTINCT
ALL
numeric )

 
FILTER ( WHERE expression )
 
OVER windowNameOrSpecification

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

{EVERY|BOOL_AND}(boolean)
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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

{ANY|SOME|BOOL_OR}(boolean)
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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 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

COUNT( { * | { [ DISTINCT|ALL ] expression } } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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

STDDEV_POP( [ DISTINCT|ALL ] numeric )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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

STDDEV_SAMP( [ DISTINCT|ALL ] numeric )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
STDDEV_SAMP (
 
DISTINCT
ALL
numeric )

 
FILTER ( WHERE expression )
 
OVER windowNameOrSpecification

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

VAR_POP( [ DISTINCT|ALL ] numeric )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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

VAR_SAMP( [ DISTINCT|ALL ] numeric )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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 no rows are selected, the result is NULL. Aggregates are only allowed in select statements.

Example:

VAR_SAMP(X)

BIT_AND

BIT_AND(expression)
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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.

Example:

BIT_AND(ID)

BIT_OR

BIT_OR(expression)
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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.

Example:

BIT_OR(ID)

SELECTIVITY

SELECTIVITY(value)
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
SELECTIVITY ( value )

 
FILTER ( WHERE expression )
 
OVER windowNameOrSpecification

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

ENVELOPE( value )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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)

Ordered Aggregate Functions

LISTAGG

{ LISTAGG ( [ DISTINCT|ALL ] string [, separatorString] [ ON OVERFLOW ERROR ] )
withinGroupSpecification }
| { GROUP_CONCAT ( [ DISTINCT|ALL ] string
[ ORDER BY { expression [ ASC | DESC ] } [,...] ]
[ SEPARATOR separatorString ] ) }
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
LISTAGG (
 
DISTINCT
ALL
string
 
, separatorString
 
ON OVERFLOW ERROR
) withinGroupSpecification

| GROUP_CONCAT (
 
DISTINCT
ALL
string
 
ORDER BY expression
 
ASC
DESC
 
, ...
 
SEPARATOR separatorString
)

 
FILTER ( WHERE expression )
 
OVER windowNameOrSpecification

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 ( [ DISTINCT|ALL ] value
[ ORDER BY { expression [ ASC | DESC ] } [,...] ] )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
ARRAY_AGG (
 
DISTINCT
ALL
value

 
ORDER BY expression
 
ASC
DESC
 
, ...
)

 
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)

Hypothetical Set Functions

RANK aggregate

RANK(value [,...])
withinGroupSpecification
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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 aggregate

DENSE_RANK(value [,...])
withinGroupSpecification
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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 aggregate

PERCENT_RANK(value [,...])
withinGroupSpecification
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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 aggregate

CUME_DIST(value [,...])
withinGroupSpecification
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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;

Inverse Distribution Functions

PERCENTILE_CONT

PERCENTILE_CONT(numeric) WITHIN GROUP (ORDER BY value [ASC|DESC])
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
PERCENTILE_CONT ( numeric ) WITHIN GROUP ( ORDER BY value
 
ASC
DESC
)

 
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

PERCENTILE_DISC(numeric) WITHIN GROUP (ORDER BY value [ASC|DESC])
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
PERCENTILE_DISC ( numeric ) WITHIN GROUP ( ORDER BY value
 
ASC
DESC
)

 
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

MEDIAN( [ DISTINCT|ALL ] value )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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

{ MODE( value ) [ ORDER BY value [ ASC | DESC ] ] }
| { MODE() WITHIN GROUP (ORDER BY expression [ ASC | DESC ]) }
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
MODE ( value )
 
ORDER BY value
 
ASC
DESC
MODE ( ) WITHIN GROUP ( ORDER BY expression
 
ASC
DESC
)

 
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(X)
MODE(X ORDER BY X)
MODE() WITHIN GROUP (ORDER BY X)

JSON Aggregate Functions

JSON_OBJECTAGG

JSON_OBJECTAGG(
{[KEY] string VALUE value} | {string : value}
[ { NULL | ABSENT } ON NULL ]
[ { WITH | WITHOUT } UNIQUE KEYS ]
)
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
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

JSON_ARRAYAGG(expression [ { NULL | ABSENT } ON NULL ])
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
JSON_ARRAYAGG ( expression
 
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)