Skip to content
Coordinator DB
View as Markdown
View as Markdown

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 and WHERE.