How does the conditional aggregation query work?

FILTER (WHERE ...) restricts one aggregate to the rows that match, while other aggregates in the same SELECT still see every row. This replaces several separate queries, or a join per breakdown, with one scan of the table. The CASE version does the same thing in databases without FILTER; a CASE with no ELSE returns null, which SUM and COUNT ignore.

What commonly goes wrong with conditional aggregation in SQL?

SUM(CASE ... ELSE 0) returns 0 where FILTER returns null for an empty set; decide which you want to show.

Conditional Aggregation in SQL

Conditional aggregation counts or sums only the rows that meet a condition, so several breakdowns come back from a single pass over the table.

Example query

This example assumes: orders(id, order_date date, channel text, status text, total_amount numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT DATE_TRUNC('month', order_date)::date AS month,
       COUNT(*) AS orders,
       COUNT(*) FILTER (WHERE status = 'refunded') AS refunded_orders,
       SUM(total_amount) FILTER (WHERE channel = 'web') AS web_sales,
       SUM(total_amount) FILTER (WHERE channel = 'store') AS store_sales
FROM orders
GROUP BY 1
ORDER BY 1;

Portable version with CASE

SELECT DATE_TRUNC('month', order_date)::date AS month,
       COUNT(*) AS orders,
       SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunded_orders,
       SUM(CASE WHEN channel = 'web' THEN total_amount ELSE 0 END) AS web_sales,
       SUM(CASE WHEN channel = 'store' THEN total_amount ELSE 0 END) AS store_sales
FROM orders
GROUP BY 1
ORDER BY 1;

How it works

FILTER (WHERE ...) restricts one aggregate to the rows that match, while other aggregates in the same SELECT still see every row.
This replaces several separate queries, or a join per breakdown, with one scan of the table.
The CASE version does the same thing in databases without FILTER; a CASE with no ELSE returns null, which SUM and COUNT ignore.

MySQL, BigQuery, SQL Server and SQLite differences

FILTER works in PostgreSQL and SQLite 3.30+. MySQL, SQL Server and BigQuery use SUM(CASE WHEN ...) or, in BigQuery, COUNTIF(condition) and SUM(IF(condition, x, 0)).

Common mistakes

SUM(CASE ... ELSE 0) returns 0 where FILTER returns null for an empty set; decide which you want to show.
Conditions that overlap double count across columns; check that the columns add up to the total if they should.
A typo in a string such as 'Web' versus 'web' silently returns zero; check the distinct values first.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

Show orders, refunded orders and web sales by month.
How many tickets were opened and how many were resolved each week?