# Calculate filtered aggregates

Use `FILTER (WHERE ...)` to select the rows admitted to one aggregate without removing rows needed by the other aggregates. A top-level `WHERE` instead filters the input for the entire grouped query.

```sql
CREATE TABLE support_payments(status TEXT, amount INTEGER);
INSERT INTO support_payments VALUES
    ('paid', 40), ('paid', 60), ('pending', 25);

SELECT count(*) AS all_payments,
       count(*) FILTER (WHERE status = 'paid') AS paid_payments,
       sum(amount) FILTER (WHERE status = 'paid') AS paid_amount
FROM support_payments
FORMAT csv;
```

The example returns three payments, two paid payments and a paid amount of 100. Add `GROUP BY` when the same measures are needed per account or day. A group can contain rows while a particular aggregate receives none, so check the aggregate's empty-input contract before choosing a fallback with `coalesce`.

Coordinator DB also provides conditional functions such as `count_if`, `sum_if` and `avg_if`. Choose one consistent style for related measures and give each result a meaningful name. Always state how NULL values should affect the condition and the aggregated expression.

See [aggregate functions](https://coordinatordb.com/docs/reference/functions/aggregate-functions) and [`WHERE`](https://coordinatordb.com/docs/reference/statements/select/where).