Index
General Aggregate Function
Binary Set Functions
Ordered Aggregate Functions
Hypothetical Set Functions
Inverse Distribution Functions
JSON Aggregate Functions
Details
General Aggregate Functions
AVG
Copy 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:
MAX
Copy 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:
MIN
Copy 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:
SUM
Copy 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:
EVERY
Copy {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:
ANY
Copy 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:
Copy ANY( NAME LIKE 'W%' )
A = (ANY(( SELECT B FROM C)))
COUNT
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
STDDEV_POP
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
STDDEV_SAMP
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
VAR_POP
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
VAR_SAMP
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
BIT_AND_AGG
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
BIT_OR_AGG
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
BIT_XOR_AGG
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
BIT_NAND_AGG
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
BIT_NOR_AGG
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
BIT_XNOR_AGG
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
ENVELOPE
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
Binary Set Functions
COVAR_POP
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
COVAR_SAMP
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
CORR
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
REGR_SLOPE
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
REGR_INTERCEPT
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
REGR_COUNT
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
REGR_R2
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
REGR_AVGX
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
REGR_AVGY
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
REGR_SXX
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
REGR_SYY
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
REGR_SXY
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
Ordered Aggregate Functions
LISTAGG
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
ARRAY_AGG
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
Hypothetical Set Functions
RANK aggregate
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
DENSE_RANK aggregate
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
PERCENT_RANK aggregate
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
CUME_DIST aggregate
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
Inverse Distribution Functions
PERCENTILE_CONT
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
PERCENTILE_DISC
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
MEDIAN
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
MODE
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
JSON Aggregate Functions
JSON_OBJECAGG
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO
JSON_ARRAYAGG
Copy AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
TODO