Aggregate functions

array_agg(x ORDER BY y DESC)
array_agg(x ORDER BY x, y, z)

aggregate_function(...) FILTER (WHERE <condition>)

SELECT array_agg(name) FILTER (WHERE name IS NOT NULL)
FROM region;

SELECT species,
       count(*) AS count
FROM iris
GROUP BY species;

species    | count
-----------+-------
setosa     |   50
virginica  |   50
versicolor |   50

SELECT species,
    count(*) AS count
FROM iris
WHERE petal_length_cm > 4
GROUP BY species;

species    | count
-----------+-------
virginica  |   50
versicolor |   34

SELECT species,
       count(*) FILTER (where petal_length_cm > 4) AS count
FROM iris
GROUP BY species;

species    | count
-----------+-------
virginica  |   50
setosa     |    0
versicolor |   34

any_value(x) -> [same as input]

SELECT sum(o.totalprice) as spend,
    any_value(c.name)
FROM tpch.tiny.orders o
JOIN tpch.tiny.customer c
ON o.custkey  = c.custkey
GROUP BY c.custkey;
ORDER BY spend;

arbitrary(x) -> [same as input]

array_agg(x) -> array<[same as input]>

avg(x) -> double

avg(time interval type) -> time interval type

bool_and(boolean) -> boolean

bool_or(boolean) -> boolean

checksum(x) -> varbinary

count(*) -> bigint

count(x) -> bigint

count_if(x) -> bigint

every(boolean) -> boolean

geometric_mean(x) -> double

listagg(x, separator) -> varchar

LISTAGG( expression [, separator] [ON OVERFLOW overflow_behaviour])
    WITHIN GROUP (ORDER BY sort_item, [...]) [FILTER (WHERE condition)]

SELECT listagg(value, ',') WITHIN GROUP (ORDER BY value) csv_value
FROM (VALUES 'a', 'c', 'b') t(value);

csv_value
-----------
'a,b,c'

SELECT listagg(value, ',' ON OVERFLOW ERROR) WITHIN GROUP (ORDER BY value) csv_value
FROM (VALUES 'a', 'b', 'c') t(value);

SELECT listagg(value, ',' ON OVERFLOW TRUNCATE '.....' WITH COUNT) WITHIN GROUP (ORDER BY value)
FROM (VALUES 'a', 'b', 'c') t(value);

SELECT id, listagg(value, ',') WITHIN GROUP (ORDER BY o) csv_value
FROM (VALUES
    (100, 1, 'a'),
    (200, 3, 'c'),
    (200, 2, 'b')
) t(id, o, value)
GROUP BY id
ORDER BY id;

 id  | csv_value
-----+-----------
 100 | a
 200 | b,c

SELECT 
    country,
    listagg(city, ',')
        WITHIN GROUP (ORDER BY population DESC)
        FILTER (WHERE population >= 10_000_000) megacities
FROM (VALUES 
    ('India', 'Bangalore', 13_700_000),
    ('India', 'Chennai', 12_200_000),
    ('India', 'Ranchi', 1_547_000),
    ('Austria', 'Vienna', 1_897_000),
    ('Poland', 'Warsaw', 1_765_000)
) t(country, city, population)
GROUP BY country
ORDER BY country;

 country |    megacities     
---------+-------------------
 Austria | NULL              
 India   | Bangalore,Chennai 
 Poland  | NULL

max(x) -> [same as input]

max(x, n) -> array<[same as x]>

max_by(x, y) -> [same as x]

max_by(x, y, n) -> array<[same as x]>

min(x) -> [same as input]

min(x, n) -> array<[same as x]>

min_by(x, y) -> [same as x]

min_by(x, y, n) -> array<[same as x]>

sum(x) -> [same as input]

bitwise_and_agg(x) -> bigint

bitwise_or_agg(x) -> bigint
Returns the bitwise OR of all input values in 2's complement representation.

histogram(x) -> map<K,bigint>

map_agg(key, value) -> map<K,V>

map_union(x(K,V)) -> map<K,V>

SELECT histogram(floor(petal_length_cm)) petal_data
FROM memory.default.iris
GROUP BY species;

        petal_data
-- {4.0=6, 5.0=33, 6.0=11}
-- {4.0=37, 5.0=2, 3.0=11}
-- {1.0=50}

SELECT map_union(petal_data) petal_data_union
FROM (
       SELECT histogram(floor(petal_length_cm)) petal_data
       FROM memory.default.iris
       GROUP BY species
       );

             petal_data_union
--{4.0=6, 5.0=2, 6.0=11, 1.0=50, 3.0=11}

multimap_agg(key, value) -> map<K,array(V)>

approx_distinct(x) -> bigint

approx_distinct(x, e) -> bigint

approx_most_frequent(buckets, value, capacity) -> map<[same as value], bigint>

approx_percentile(x, percentage) -> [same as x]

approx_percentile(x, percentages) -> array<[same as x]>

approx_percentile(x, w, percentage) -> [same as x]

approx_percentile(x, w, percentages) -> array<[same as x]>

approx_set(x) -> HyperLogLog

merge(x) -> HyperLogLog

merge(qdigest(T)) -> qdigest(T)

merge(tdigest) -> tdigest

numeric_histogram(buckets, value) -> map<double, double>

numeric_histogram(buckets, value, weight) -> map<double, double>

Yael Ben-Haim and Elad Tom-Tov, "A streaming parallel decision tree algorithm",
J. Machine Learning Research 11 (2010), pp. 849--872.

qdigest_agg(x) -> qdigest([same as x])

qdigest_agg(x, w) -> qdigest([same as x])

qdigest_agg(x, w, accuracy) -> qdigest([same as x])

tdigest_agg(x) -> tdigest

tdigest_agg(x, w) -> tdigest

corr(y, x) -> double

covar_pop(y, x) -> double

covar_samp(y, x) -> double

kurtosis(x) -> double

kurtosis(x) = n(n+1)/((n-1)(n-2)(n-3))sum[(x_i-mean)^4]/stddev(x)^4-3(n-1)^2/((n-2)(n-3))

regr_intercept(y, x) -> double

regr_slope(y, x) -> double

skewness(x) -> double

stddev(x) -> double

stddev_pop(x) -> double

stddev_samp(x) -> double

variance(x) -> double

var_pop(x) -> double

var_samp(x) -> double

reduce_agg(inputValue T, initialState S, inputFunction(S, T, S), combineFunction(S, S, S)) -> S

SELECT id, reduce_agg(value, 0, (a, b) -> a + b, (a, b) -> a + b)
FROM (
    VALUES
        (1, 3),
        (1, 4),
        (1, 5),
        (2, 6),
        (2, 7)
) AS t(id, value)
GROUP BY id;
-- (1, 12)
-- (2, 13)

SELECT id, reduce_agg(value, 1, (a, b) -> a * b, (a, b) -> a * b)
FROM (
    VALUES
        (1, 3),
        (1, 4),
        (1, 5),
        (2, 6),
        (2, 7)
) AS t(id, value)
GROUP BY id;
-- (1, 60)
-- (2, 42)