▲
Search:
| Highlight keyword(s)
Home Download Cheat Sheet Documentation Quickstart Installation Tutorial Features Security Performance Advanced Reference Commands Functions • Aggregate • Window Data Types SQL Grammar System Tables Javadoc PDF (2 MB) Support FAQ Error Analyzer Google Group Appendix History License Build Links MVStore Architecture Migration to 2.0 |  | # 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
    AVG ( [ DISTINCT|ALL ] { numeric | interval } )
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`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
    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 | interval | { boolean } } )
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`SUM (` |
| `DISTINCT` |
| `ALL` |
| `numeric` |
| `interval` |
| `boolean` |
| `)`
`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 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
    {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 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
    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 less than two 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 less than two rows are selected, the result is `NULL`. Aggregates are only allowed in select statements. Example: VAR_SAMP(X) ### ANY_VALUE
    ANY_VALUE( [ DISTINCT|ALL ] value )
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`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_AGG}|{BIT_AND}}(expression)
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
| `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_AGG}|{BIT_OR}}(expression)
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
| `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
    BIT_XOR_AGG( [ DISTINCT|ALL ] expression)
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`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
    BIT_NAND_AGG(expression)
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`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
    BIT_NOR_AGG(expression)
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`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
    BIT_XNOR_AGG( [ DISTINCT|ALL ] expression)
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`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
    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) ## Binary Set Functions ### COVAR_POP
    COVAR_POP(dependentExpression, independentExpression)
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`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
    COVAR_SAMP(dependentExpression, independentExpression)
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`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
    CORR(dependentExpression, independentExpression)
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`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
    REGR_SLOPE(dependentExpression, independentExpression)
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`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
    REGR_INTERCEPT(dependentExpression, independentExpression)
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`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
    REGR_COUNT(dependentExpression, independentExpression)
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`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
    REGR_R2(dependentExpression, independentExpression)
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`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
    REGR_AVGX(dependentExpression, independentExpression)
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`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
    REGR_AVGY(dependentExpression, independentExpression)
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`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
    REGR_SXX(dependentExpression, independentExpression)
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`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
    REGR_SYY(dependentExpression, independentExpression)
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`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
    REGR_SXY(dependentExpression, independentExpression)
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`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) ## Ordered Aggregate Functions ### LISTAGG
    LISTAGG ( [ DISTINCT|ALL ] string [, separatorString]
    [ ON OVERFLOW { ERROR
    | TRUNCATE [ filterString ] { WITH | WITHOUT } COUNT } ] )
    withinGroupSpecification
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`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
    ARRAY_AGG ( [ DISTINCT|ALL ] value
    [ ORDER BY sortSpecificationList ] )
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`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) ## 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 sortSpecification)
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`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
    PERCENTILE_DISC(numeric) WITHIN GROUP (ORDER BY sortSpecification)
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`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
    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() WITHIN GROUP (ORDER BY sortSpecification) }
    | { MODE( value [ ORDER BY sortSpecification ] ) }
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`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 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( [ DISTINCT|ALL ] expression
    [ ORDER BY sortSpecificationList ]
    [ { NULL | ABSENT } ON NULL ] )
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`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)