Skip to content
Coordinator DB
View as Markdown
View as Markdown

Aggregate Functions ​

Aggregate functions calculate one result from several input rows. Without a GROUP BY clause, they aggregate the whole input. With GROUP BY, they calculate one result for each group.

CoordinatorDB also supports:

  • Combinators, which add a condition or produce and combine intermediate aggregate states.
  • DISTINCT, which removes duplicate argument values before aggregation.
  • FILTER (WHERE condition), which selects the rows admitted to an aggregate.
  • ORDER BY inside an aggregate call, which determines the order of its input.
  • Window functions, which apply aggregates to a window while keeping individual result rows.

NULL processing ​

Numeric and statistical aggregates skip NULL arguments. An aggregate with two numeric arguments, such as covar_pop(y, x), skips a row if either argument is NULL. The remaining complete pairs determine both means and the count.

Collection aggregates have their own rules. In particular, array_agg and json_agg retain NULL values. count(*) counts every row, whereas count(column) counts only rows in which that column is not NULL.

Examples:

Create a table containing both present and missing values:

sql
CREATE TABLE t_null_big (x Int32, y Int32);

INSERT INTO t_null_big VALUES
    (1, 2),
    (2, NULL),
    (3, 2),
    (3, 3),
    (3, NULL);

The table contains:

sql
SELECT x, y FROM t_null_big ORDER BY rowid FORMAT csv;
text
1,2
2,\N
3,2
3,3
3,\N

To total the values in the y column, use sum:

sql
SELECT sum(y) FROM t_null_big FORMAT csv;
text
7

Use array_agg to collect the values into an array. Unlike numeric aggregates, it retains missing values:

sql
SELECT array_agg(y ORDER BY rowid) FROM t_null_big FORMAT csv;
text
"[2,null,2,3,null]"

Use FILTER when the collection should contain only present values:

sql
SELECT array_agg(y ORDER BY rowid) FILTER (WHERE y IS NOT NULL)
FROM t_null_big
FORMAT csv;
text
"[2,2,3]"

Use coalesce to replace NULL with a value appropriate for the calculation. Replacing a missing measurement with zero makes it contribute to both the sum and the number of measurements in the average:

sql
SELECT round(avg(y), 6), avg(coalesce(y, 0))
FROM t_null_big
FORMAT csv;
text
2.333333,1.4

An Array or Tuple containing NULL is not itself SQL NULL. Collection and tuple functions preserve those member values according to the declared member types. There is no need to wrap a value in a tuple to make array_agg retain it.

count() and count(1) count rows independently of their column values. count(y) counts only present values, including when missing values form their own group:

sql
SELECT y, count(1), count(y)
FROM t_null_big
GROUP BY y
ORDER BY y NULLS FIRST
FORMAT csv;
text
\N,2,0
2,2,2
3,1,1

The window functions first_value and last_value select a row from their window frame and retain NULL when the selected row contains it. They do not accept IGNORE NULLS or RESPECT NULLS modifiers.

Empty input ​

Most aggregates, including sum, avg, min, max, and array_agg, return NULL when no value contributes. count returns zero and total returns 0.0. Conditional aggregates have their own empty-input behavior: count_if and sum_if return zero, while avg_if returns NULL.

sql
SELECT count(y), sum(y), avg(y), total(y)
FROM t_null_big
WHERE x > 100
FORMAT csv;
text
0,\N,\N,0.0

A grouped query produces no result rows if its input contains no groups.

Aggregate reference ​

See the aggregate function reference for the supported functions and their argument, result, and empty-input rules.