Aggregate Functions

Aggregate functions calculate groupd records retrieved by a select query. If records are not grouped, all records are dealt with as one group.

If distinct option is specified, aggregate functions calculate only unique values.

Aggregate Functions can be used only in Select Clause, Having Clause and Order By Clause

name	description
COUNT	Return the number of values
MIN	Return the minimum value
MAX	Return the maximum value
SUM	Return the sum of values
AVG	Return the average of values
STDEV	Return the sample standard deviation of values
STDEVP	Return the population standard deviation of values
VAR	Return the sample variance of values
VARP	Return the population variance of values
MEDIAN	Return the median of values
LISTAGG	Return the concatenated string of values
JSON_AGG	Return the string formatted in JSON array

Definitions

COUNT

COUNT([DISTINCT] expr)

expr: value
return: integer

Returns the number of non-null values of expr.

COUNT([DISTINCT] *)

return: integer

Returns the number of all values including null values.

MIN

MIN(expr)

expr: value
return: primitive type

Returns the minimum value of non-null values of expr. If all values are null, then returns null.

MAX

MAX(expr)

expr: value
return: primitive type

Returns the maximum value of non-null values of expr. If all values are null, then return a null.

SUM

SUM([DISTINCT] expr)

expr: value
return: float

Returns the sum of float values of expr. If all values are null, then returns a null.

AVG

AVG([DISTINCT] expr)

expr: value
return: float

Returns the average of float values of expr. If all values are null, then returns a null.

STDEV

STDEV([DISTINCT] expr)

expr: value
return: float

Returns the sample standard deviation of float values of expr. If all values are null, then returns a null.

STDEVP

STDEVP([DISTINCT] expr)

expr: value
return: float

Returns the population standard deviation of float values of expr. If all values are null, then returns a null.

VAR

VAR([DISTINCT] expr)

expr: value
return: float

Returns the sample variance of float values of expr. If all values are null, then returns a null.

VARP

VARP([DISTINCT] expr)

expr: value
return: float

Returns the population variance of float values of expr. If all values are null, then returns a null.

MEDIAN

MEDIAN([DISTINCT] expr)

expr: value
return: float

Returns the median of float or datetime values of expr. If all values are null, then returns a null.

Even if expr represents datetime values, this function returns a float or integer value. The return value can be converted to a datetime value by using the DATETIME function.

LISTAGG

LISTAGG([DISTINCT] expr [, separator]) [WITHIN GROUP (order_by_clause)]

expr: value
separator: string
order_by_clause: Order By Clause
return: string

Returns the string result with the concatenated non-null values of expr. If all values are null, then returns a null.

separator is placed between values. Empty string is the default. By using order_by_clause, you can sort values.

JSON_AGG

JSON_AGG([DISTINCT] expr) [WITHIN GROUP (order_by_clause)]

expr: value
order_by_clause: Order By Clause
return: string

Returns the string formatted in JSON array of expr.