Aggregate Functions

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

We support all the well-known standard SQL aggregate functions: COUNT(*|...), COUNT(DISTINCT ...), SUM(...), AVG(...), MIN(...), MAX(...) and even PROD(...) over scalar types/expressions and groupings.

In addition, many important statistical aggregate functions are supported.

Also character string aggregate functions LISTAGG and GROUP_CONCAT and json.tojsonarray() are available.

For the examples below to create the shown results, we use a simple table t with data:

CREATE TABLE t(i int, c varchar(8));
INSERT INTO t(i,c) VALUES (1,'one'), (2,'two'), (3,'tree'), (4,'four'), (NULL,NULL);

Standard aggregate functions

Aggregate functions returning a concatenated CLOB string

The SQL:2016 standard LISTAGG is supported. It is equivalent to sys.group_concat aggregate function. In the below examples you can simply replace sys.group_concat with LISTAGG and get the same results. LISTAGG does not require the sys. prefix.

For the examples below we add some data to the example table t:

INSERT INTO t(i,c) VALUES
(1, ''), (1, 'kwik'),
(2, NULL), (2, 'kwek'),
(3, ' '), (3, 'kwak'),
(4, 'kwak'), (4, ' ');

to create the shown Results.

Note: for the GROUP_CONCAT examples above without an ORDER BY condition, we have shown only one of their correct result. Without an ordering, the input values can be concatenated in any order. In addition, depending on the implementation, every reexecution of the same function can produce a different but correct result. To demonstrate this, we show different results for sys.group_concat(c) and sys.group_concat(ALL c), even though they are essentially the same function.

Note: You must include the json. prefix to these aggregate functions for them to work properly.

Aggregate functions for statistics

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

For sys.quantile the percentile argument is a float value between 0.0 and 1.0.
sys.median(expr) is equivalent to sys.quantile(expr, 0.5).

sys.stddev_samp computes the cumulative sample standard deviation and returns the square root of the sample variance. sys.stddev_pop computes the population standard deviation and returns the square root of the population variance. Both functions take as an argument any numeric datatype. Likewise, sys.var_samp and sys.var_pop functions return the sample variance (/n-1) of a set of numbers and the biased variance (/n) of a set of numbers, respectively.

The aggregate functions sys.median_avg and sys.quantile_avg return the interpolated value if the median/quantile doesn't fall exactly on a particular row. These functions always return a value of type DOUBLE and only work for numeric types (various width integers, decimal and floating point).

Checksum aggregate functions

Cryptographic hash function aggregates to calculate a checksum (digest) over a column or group of strings.

Note It only makes sense to use these with an ordering, as SELECT sha256(name ORDER BY id) FROM table, since the ordering in which the values are fed into the digest algorithm is important and cannot otherwise be guaranteed.

SHA-1, SHA-224, SHA-256, SHA-384, SHA-512 and RIPEMD-160 are cryptographic hash algorithms.

Examples

create schema aggr_tst;
set schema aggr_tst;

create table aggr_tst.tc (c real);
insert into aggr_tst.tc values (1), (2), (3), (4), (5), (9);
select * from aggr_tst.tc;

select
  count(*) as countstar
, count(c) as count
, count(distinct c) as countdistinct
, sum(c) as sum
, avg(c) as average
, prod(c) as product
, min(c) as minimum
, max(c) as maximum
, group_concat(c,',') as group_concat
, listagg(c,',' order by c desc) as listagg
, median(c) as median
, median_avg(c) as median_avg
, quantile(c, 0.5) as quantile
, quantile_avg(c, 0.5) as quantile_avg
, stddev_samp(c) as stddev_samp
, stddev_pop(c) as stddev_pop
, var_samp(c) as var_samp
, var_pop(c) as var_pop
, corr(c, c+1) as corr
, covar_samp(c, c*2) as covar_samp
, covar_pop(c, c*2) as covar_pop
from aggr_tst.tc;

drop table aggr_tst.tc;

set schema sys;
drop schema aggr_tst;

select type, sha256(name ORDER BY id) from sys.tables group by type;

Tip: To list all 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;

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