FILTER and Conditional Aggregation

MySQL 8.4 has no FILTER clause; you count subsets in one pass with CASE inside the aggregate, which is what FILTER desugars to anyway.

SyntaxSUM(CASE WHEN condition THEN expr ELSE 0 END) | COUNT(CASE WHEN condition THEN 1 END) | SUM(condition)

Standard SQL lets you restrict a single aggregate to a subset of the group: COUNT(*) FILTER (WHERE status = 'paid'). MySQL 8.4 does not implement FILTER — it is not in the aggregate function syntax and will fail to parse. PostgreSQL and SQLite have it; if you are porting a query from either, you must rewrite it.

The CASE form

The portable technique, older than FILTER and available everywhere, is to put the condition inside the aggregate so non-matching rows contribute nothing:

COUNT(*) FILTER (WHERE cond)   -- standard SQL, not MySQL
COUNT(CASE WHEN cond THEN 1 END)  -- MySQL: the same result
SUM(cond)                          -- shorthand: TRUE is 1, FALSE is 0

All three count matching rows. The third works because a MySQL comparison evaluates to 1, 0 or NULL, and SUM adds those up while ignoring NULL. AVG(cond) is the matching shorthand for a ratio.

Why one pass matters

Conditional aggregation computes every subset in a single scan. The alternative — one subquery or one UNION branch per condition — reads the table once per metric. It is also how you pivot rows into columns: one SUM(CASE ...) per output column.

The trap

Use COUNT with a CASE that has no ELSE. A missing WHEN yields NULL, which COUNT skips. Add ELSE 0 and COUNT counts the zeros too, returning the group size for every column. With SUM the ELSE 0 is harmless.

Example

Example · sql
-- Order status breakdown per month in a single pass over the table.
SELECT
    DATE_FORMAT(placed_at, '%Y-%m')                        AS month,
    COUNT(*)                                               AS orders_total,
    -- Longhand: the CASE yields NULL for non-matching rows, and COUNT skips NULL.
    COUNT(CASE WHEN status = 'paid'      THEN 1 END)       AS paid,
    COUNT(CASE WHEN status = 'refunded'  THEN 1 END)       AS refunded,
    -- Shorthand: the comparison is 1 or 0, and SUM adds them. Same number.
    SUM(status = 'cancelled')                              AS cancelled,
    -- Conditional SUM: only paid rows contribute to the revenue column.
    SUM(CASE WHEN status = 'paid' THEN amount_cents ELSE 0 END) AS paid_cents,
    -- Conditional AVG: average of paid orders only, NULL excluded from the divisor.
    AVG(CASE WHEN status = 'paid' THEN amount_cents END)   AS avg_paid_cents,
    -- Ratio, 0.0 to 1.0.
    ROUND(AVG(status = 'refunded'), 4)                     AS refund_rate
FROM orders
WHERE placed_at >= '2026-01-01'
GROUP BY month
ORDER BY month;

When to use it

  • Break a status column into one count column per status in a single query.
  • Compute a conversion or refund rate as AVG of a boolean condition.
  • Pivot attribute rows into columns for a report or CSV export.
  • Port a PostgreSQL query that uses FILTER (WHERE ...) to MySQL.

More examples

Porting a FILTER query from PostgreSQL

The mechanical translation: FILTER (WHERE c) on COUNT becomes CASE WHEN c THEN 1 END with no ELSE; on SUM it becomes CASE WHEN c THEN expr ELSE 0 END. Keep this next to any cross-database query you maintain — the rewrite is always the same shape.

Example · sql
-- PostgreSQL / SQLite / standard SQL — rejected by MySQL 8.4:
--   SELECT team_id,
--          COUNT(*) FILTER (WHERE result = 'win')  AS wins,
--          SUM(points) FILTER (WHERE home = TRUE)  AS home_points
--   FROM games GROUP BY team_id;

-- MySQL 8.4 equivalent, line for line:
SELECT
    team_id,
    COUNT(CASE WHEN result = 'win' THEN 1 END)          AS wins,
    SUM(CASE WHEN home = TRUE THEN points ELSE 0 END)   AS home_points
FROM games
GROUP BY team_id;

Pivoting rows into columns

MySQL has no PIVOT operator, so this is the pivot. MAX (or MIN) is used rather than SUM because there is at most one value per cell and you want the value, not a total; the missing ELSE leaves empty cells as NULL. The final column is worth keeping while you verify the assumption that each cell really is unique.

Example · sql
-- Source rows: one per (student, subject). Wanted: one row per student.
SELECT
    student_id,
    MAX(CASE WHEN subject = 'maths'   THEN score END) AS maths,
    MAX(CASE WHEN subject = 'physics' THEN score END) AS physics,
    MAX(CASE WHEN subject = 'history' THEN score END) AS history,
    COUNT(*)                                          AS subjects_taken,
    -- Guard: did any student have two rows for the same subject?
    COUNT(DISTINCT subject) = COUNT(*)                AS no_duplicates
FROM scores
GROUP BY student_id;

Counting subsets in one pass versus several

The performance argument for conditional aggregation, and the NULL caveat that comes with the SUM(condition) shorthand. Use the single-pass form whenever the metrics share a table and a time range; reach for <=> when NULL is itself one of the buckets you are counting.

Example · sql
-- Three scans of events, one per metric.
SELECT
    (SELECT COUNT(*) FROM events WHERE type = 'view')  AS views,
    (SELECT COUNT(*) FROM events WHERE type = 'click') AS clicks,
    (SELECT COUNT(*) FROM events WHERE type = 'buy')   AS buys;

-- One scan, same three numbers.
SELECT
    SUM(type = 'view')  AS views,
    SUM(type = 'click') AS clicks,
    SUM(type = 'buy')   AS buys
FROM events;

-- Careful with NULL: if type is NULL the comparison is NULL, SUM skips it,
-- and the row is counted in no column. Make that explicit when it matters:
SELECT SUM(type <=> NULL) AS untyped FROM events;  -- <=> is NULL-safe equality

Related

sql casesql countsql group bysql havingsql pivot

Discussion

  • Be the first to comment on this lesson.