Aggregate Functions

Aggregate functions operate on a column or column expression of a table or group (as created via GROUP BY clause).

For the examples below we use a simple table t(i int, c varchar(8)) with contents: INSERT into t(i,c) VALUES (1, 'one'), (2, 'two'), (3, 'tree'), (4, 'four'), (NULL, NULL); to create the shown Results.

Standard aggregate functions

FunctionReturn typeDescriptionExampleResult
avg(numeric_expr)doublecompute the average of the non-NULL integer/float/decimal/month_interval values in the column or groupavg(i)2.5
count(*)bigintcount the number of rows in the column or group including those who may have NULL valuescount(*)5
count(col_expr)bigintcount the number of non-NULL values in the column or groupcount(i)4
count(distinct col_expr)bigintcount the number of distinct non-NULL values in the column or groupcount(distinct i%2)2
max(col_expr)type of col_exprreturn the maximum non-NULL value in the column or groupmax(c)two
min(col_expr)type of col_exprreturn the minimum non-NULL value in the column or groupmin(c)four
prod(numeric_expr)bigint or hugeint or doublecompute the product of the non-NULL numeric values in the column or groupprod(i)24
prod(distinct numeric_expr)bigint or hugeint or doublecompute the product of the distinct non-NULL numeric values in the column or groupprod(distinct 1 + i%2)2
sum(integer_expr)bigint or hugeintcompute the sum of all non-NULL integer values in the column or groupsum(i)10
sum(distinct integer_expr)bigint or hugeintcompute the sum of all distinct non-NULL integer values in the column or groupsum(distinct i/2)3
sum(decimal_expr)decimalcompute the sum of all non-NULL decimal values in the column or groupsum(cast(i as decimal))10.000
sum(float_expr)real or doublecompute the sum of all non-NULL floating point values in the column or groupsum(cast(i as real))10.0
sum(month_interval_expr)month_intervalcompute the sum of all non-NULL interval month values in the column or groupsum(cast(i as interval month))10
sum(sec_interval_expr)sec_intervalcompute the sum of all non-NULL interval second values in the column or groupsum(cast(i as interval second))10.000

Aggregate functions returning a concatenated CLOB string

Note: You must include the sys. or json. prefix for these aggregate functions (see examples) in order to work properly.

FunctionReturn typeDescriptionExampleResult
listagg(col_expr)varcharconcatenate non-NULL values in a column or group into one large varchar string with values separated by a comma (the default separator) Supported from release Jun2020 (11.37.7)listagg(c)one,two,tree,four
listagg(col_expr, separator_str)varcharconcatenate non-NULL values in a column or group into one large varchar string with values separated by separator_str Supported from release Jun2020 (11.37.7)listagg(i, ' | ')1 | 2 | 3 | 4
sys.group_concat(col_expr)clobconcatenate non-NULL values in a column or group into one large string with values separated by a comma (the default separator)sys.group_concat(c)one,two,tree,four
sys.group_concat(col_expr, separator_str)clobconcatenate non-NULL values in a column or group into one large string with values separated by separator_strsys.group_concat(i, ' | ')1 | 2 | 3 | 4
json.tojsonarray(clob)clobconvert string values in the column or group into a json array stringjson.tojsonarray(c)[ "one", "two", "tree", "four" ]
json.tojsonarray(double)clobconvert numeric values in the column or group into a json array stringjson.tojsonarray(i)[ "1", "2", "3", "4" ]

Aggregate functions for statistics

Note: You must include the sys. prefix for below aggregate functions (see examples) in order to work properly.

Statistic FunctionReturn typeDescriptionExampleResult
sys.corr(numeric_a, numeric_b)doublereturn the correlation coefficient of the non-NULL values in the numeric (integer/decimal/real/double) column or group numeric_a versus numeric_bsys.corr(i, i+2)0.7500000000000001
sys.covar_pop(numeric_a, numeric_b)doublereturn the population covariance of numeric_expr of the non-NULL values in the numeric (integer/decimal/real/double/interval) column or group numeric_a versus numeric_b.Supported from release Jun2020 (11.37.7)sys.covar_pop(i, i*2)2.5
sys.covar_samp(numeric_a, numeric_b)doublereturn the sample covariance of numeric_expr of the non-NULL values in the numeric (integer/decimal/real/double/interval) column or group numeric_a versus numeric_b.Supported from release Jun2020 (11.37.7)sys.covar_samp(i, i*2)3.3333333333333335
sys.median(col_expr)type of col_exprreturn the statistic median of the non-NULL values in the numeric (integer/decimal/real/double) or temporal (date/time/timestamp/interval) column or groupsys.median(i)2
sys.median_avg(numeric_expr)doublereturn the statistic median average of the non-NULL values in the numeric (integer/decimal/real/double) column or groupsys.median_avg(i)2.5
sys.quantile(col_expr, percentile)type of col_exprreturn the statistic quantile of the non-NULL values in the numeric (integer/decimal/real/double) or temporal (date/time/timestamp/interval) column or group. The percentile argument must be between 0.0 and 1.0sys.quantile(i, 0.7)3
sys.quantile_avg(numeric_expr, percentile)doublereturn the statistic quantile average of the non-NULL values in the numeric (integer/decimal/real/double) column or group. The percentile argument must be between 0.0 and 1.0sys.quantile_avg(i, 0.6)2.8
sys.stddev_pop(numeric_expr)doublereturn the population standard deviation of numeric_expr of the non-NULL values in the numeric (integer/decimal/real/double/interval) column or group. Same as the square root of var_pop().sys.stddev_pop(i)1.118033988749895
sys.stddev_samp(numeric_expr)doublereturn the sample standard deviation of numeric_expr of the non-NULL values in the numeric (integer/decimal/real/double/interval) column or group. Same as the square root of var_samp().sys.stddev_samp(i)1.2909944487358056
sys.var_pop(numeric_expr)doublereturn the population standard variance of numeric_expr of the non-NULL values in the numeric (integer/decimal/real/double/interval) column or group. Same as the square of stddev_pop().sys.var_pop(i)1.25
sys.var_samp(numeric_expr)doublereturn the sample standard variance of numeric_expr of the non-NULL values in the numeric (integer/decimal/real/double/interval) column or group. Same as the square of stddev_samp().sys.var_samp(i)1.6666666666666667

You can also create your own aggregate functions, see Create Aggregate function command.

To list the available aggregate functions in your MonetDB server run query:

SELECT DISTINCT schema_id, name, func, mod, language, system FROM sys.functions WHERE type = 3 ORDER BY name;