# Aggregate combinators

Aggregate combinators are named variants of an aggregate. A suffix changes whether the function filters input rows, returns an intermediate state, or combines previously calculated states.

CoordinatorDB provides the specific variants listed on this page. Names use underscores, such as `sum_if` and `avg_merge_state`.

## \_if

Conditional aggregates process only rows whose condition is SQL true. They let one query calculate totals for several segments without separate subqueries or joins.

```text
count_if(condition)
sum_if(value, condition)
avg_if(value, condition)
```

`count_if` counts matching rows. `sum_if` and `avg_if` use the same numeric conversion and overflow rules as [`sum`](https://coordinatordb.com/docs/reference/functions/aggregate-functions/sum) and [`avg`](https://coordinatordb.com/docs/reference/functions/aggregate-functions/avg); selected `NULL` values are skipped. False and `NULL` conditions do not contribute.

If no non-`NULL` value contributes, `count_if` and `sum_if` return `0`, while `avg_if` returns `NULL`.

The arguments are evaluated before the aggregate tests the condition. A false condition does not protect a value expression from an evaluation error.

### Comparing order segments

```sql
CREATE TABLE orders (status String, amount UInt32);
INSERT INTO orders VALUES
    ('paid', 100), ('paid', 60), ('pending', 40), ('cancelled', 20);
```

```sql
SELECT count_if(status = 'paid') AS paid_orders,
       sum_if(amount, status = 'paid') AS paid_amount,
       sum_if(amount, status = 'pending') AS pending_amount,
       avg_if(amount, status = 'paid') AS average_paid_order
FROM orders
FORMAT csv;
```

```text
2,160,40,80.0
```

Use the SQL `FILTER (WHERE condition)` clause for other aggregates. `sum(value) FILTER (...)` returns `NULL` when no value contributes; `sum_if` returns `0`.

## \_state

The `_state` variant returns the information needed to continue an aggregation, rather than only its final answer. The state has an [`AggregateFunction`](https://coordinatordb.com/docs/reference/data-types/aggregatefunction) type containing the aggregate name and its argument types.

For example, an average state retains both a total and a count. Combining these states weights each batch by its number of observations; averaging batch averages would lose that information.

| Aggregate | Build a state                           | Combine states and return a value | Combine states and return a state |
| --------- | --------------------------------------- | --------------------------------- | --------------------------------- |
| `sum`     | `sum_state(value)`                      | `sum_merge(state)`                | `sum_merge_state(state)`          |
| `count`   | `count_state()` or `count_state(value)` | `count_merge(state)`              | `count_merge_state(state)`        |
| `avg`     | `avg_state(value)`                      | `avg_merge(state)`                | `avg_merge_state(state)`          |
| `min`     | `min_state(value)`                      | `min_merge(state)`                | `min_merge_state(state)`          |
| `max`     | `max_state(value)`                      | `max_merge(state)`                | `max_merge_state(state)`          |

`finalize_aggregation(state)` is a scalar function that reads the final value of one state. It does not combine several rows.

### Combining average states

```sql
CREATE TABLE readings(batch UInt8, value Int64);
INSERT INTO readings VALUES (1, 10), (1, 20), (2, 90);

CREATE TABLE batch_averages(batch UInt8, state AggregateFunction(avg, Int64));
INSERT INTO batch_averages
SELECT batch, avg_state(value)
FROM readings
GROUP BY batch;
```

Read each batch's average:

```sql
SELECT batch, finalize_aggregation(state) AS average_value
FROM batch_averages
ORDER BY batch
FORMAT csv;
```

```text
1,15.0
2,90.0
```

## \_merge

The `_merge` variant combines matching intermediate states and returns their
final aggregate value. Combine the states from the previous example to calculate
the mean of all three observations:

```sql
SELECT avg_merge(state) AS overall_average
FROM batch_averages
FORMAT csv;
```

```text
40.0
```

## \_merge_state

The `_merge_state` variant combines matching states and returns another state
instead of a final value. Use `avg_merge_state` to retain a combined average for
another stage:

```sql
SELECT finalize_aggregation(avg_merge_state(state))
FROM batch_averages
FORMAT csv;
```

```text
40.0
```

### State signatures and empty input

States can be merged only when the aggregate name and every argument type match exactly. A mismatched signature raises `TYPE_MISMATCH`. Declared column and cast types are retained even for an empty input.

Empty count and sum states finalize to zero. Empty minimum and maximum states finalize to their argument type's default. An empty average state finalizes to `NULL`. These state results differ from ordinary `sum`, `min` and `max` on empty input, which return `NULL`.

States use CoordinatorDB's native representation. They cannot be exchanged with ClickHouse aggregate-state bytes.

## \_simple_state

`_simple_state` returns a scalar partial result wrapped in a [`SimpleAggregateFunction`](https://coordinatordb.com/docs/reference/data-types/simpleaggregatefunction) type:

```text
sum_simple_state(value)
min_simple_state(value)
max_simple_state(value)
```

The partial value can be read directly and combined with ordinary `sum`, `min` or `max`; no `_merge` or finalization call is required. Inserting a partial result does not automatically combine table rows.

### Combining partial totals

```sql
CREATE TABLE partials
(
    batch UInt8,
    subtotal SimpleAggregateFunction(sum, Int64)
);

INSERT INTO partials
SELECT batch, sum_simple_state(value)
FROM readings
GROUP BY batch;
```

```sql
SELECT sum(subtotal) AS total_value
FROM partials
FORMAT csv;
```

```text
120
```

Use full intermediate states for averages and counts. Arbitrary suffix combinations, such as array or map combinators, are not supported.