Aggregate

SQL Aggregate Functions

Index

General Aggregate Function

• AVG

• MAX

• MIN

• SUM

• EVERY

• ANY

• COUNT

• STDDEV_POP

• STDDEV_SAMP

• VAR_POP

• VAR_SAMP

• 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_COUNT
• PERCENTILE_DISC
• MEDIAN
• MODE

JSON Aggregate Functions

• JSON_OBJECTAGG
• JSON_ARRAYAGG

Details

General Aggregate Functions

AVG

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

Return average (mean) value. If no rows are selected, the result is NULL. Aggregation 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.

Examples:

AVG(X)

MAX

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

The highest value. If no rows are selected, the result is NULL. Aggregate are only allowed in

select statements. The returned value is of the same data type are the parameter.

Examples:

MAX(X)

MIN

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.

Examples:

MIN(X)

SUM

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.

Examples:

SUM(X)

EVERY

{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.

Examples:

EVERY(ID>10)

ANY

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[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.

Examples:

ANY(NAME LIKE 'W%')
A = (ANY((SELECT B FROM C)))

COUNT

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

TODO

STDDEV_POP

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

TODO

STDDEV_SAMP

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

TODO

VAR_POP

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

TODO

VAR_SAMP

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

TODO

BIT_AND_AGG

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

TODO

BIT_OR_AGG

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

TODO

BIT_XOR_AGG

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

TODO

BIT_NAND_AGG

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

TODO

BIT_NOR_AGG

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

TODO

BIT_XNOR_AGG

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

TODO

ENVELOPE

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

TODO

Binary Set Functions

COVAR_POP

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

TODO

COVAR_SAMP

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

TODO

CORR

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

TODO

REGR_SLOPE

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

TODO

REGR_INTERCEPT

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

TODO

REGR_COUNT

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

TODO

REGR_R2

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

TODO

REGR_AVGX

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

TODO

REGR_AVGY

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

TODO

REGR_SXX

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

TODO

REGR_SYY

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

TODO

REGR_SXY

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

TODO

Ordered Aggregate Functions

LISTAGG

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

TODO

ARRAY_AGG

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

TODO

Hypothetical Set Functions

RANK aggregate

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

TODO

DENSE_RANK aggregate

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

TODO

PERCENT_RANK aggregate

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

TODO

CUME_DIST aggregate

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

TODO

Inverse Distribution Functions

PERCENTILE_CONT

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

TODO

PERCENTILE_DISC

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

TODO

MEDIAN

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

TODO

MODE

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

TODO

JSON Aggregate Functions

JSON_OBJECAGG

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

TODO

JSON_ARRAYAGG

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

TODO