# Aggregate Functions

Aggregate functions calculate one result from several input rows. Without a
[`GROUP BY`](https://coordinatordb.com/docs/reference/statements/select/group-by) clause, they aggregate the
whole input. With `GROUP BY`, they calculate one result for each group.

CoordinatorDB also supports:

* [Combinators](https://coordinatordb.com/docs/reference/functions/aggregate-functions/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](https://coordinatordb.com/docs/reference/functions/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`](https://coordinatordb.com/docs/reference/functions/aggregate-functions/sum):

```sql
SELECT sum(y) FROM t_null_big FORMAT csv;
```

```text
7
```

Use [`array_agg`](https://coordinatordb.com/docs/reference/functions/aggregate-functions/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`](https://coordinatordb.com/docs/reference/functions/regular-functions/functions-for-nulls#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`](https://coordinatordb.com/docs/reference/data-types/array) or
[`Tuple`](https://coordinatordb.com/docs/reference/data-types/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`](https://coordinatordb.com/docs/reference/functions/window-functions/first-value)
and [`last_value`](https://coordinatordb.com/docs/reference/functions/window-functions/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](https://coordinatordb.com/docs/reference/functions/aggregate-functions/reference-index) for the supported
functions and their argument, result, and empty-input rules.