Conditional aggregation

This is the exact file agents read, including the credit header and licence notice. Save it as conditional-aggregation/SKILL.md.

---
name: conditional-aggregation
description: >
  Write correct conditional aggregation and pivots in SQL, counting and summing subsets inside one grouped scan and turning rows into columns, across PostgreSQL, MySQL and MariaDB, SQLite and SQL Server. Covers the FILTER (WHERE ...) clause, the SUM/COUNT/MAX(CASE WHEN ...) fallback, the ELSE 0 versus no-ELSE rule that decides whether a count or average is correct, fixed and dynamic pivot columns, two-dimensional pivots, and native PIVOT and crosstab. Use it when someone says "pivot rows to columns", "turn rows into columns", "count by status in one query", "conditional count or sum", "SUM CASE WHEN", "COUNTIF in SQL", "FILTER clause", "how do I make a pivot table in SQL", or "several metrics in one query". Do NOT use it for plain single-value GROUP BY totals (see grouping-aggregation), for NULL semantics in general (see null-handling-in-sql), for running totals and per-row ranking (see window-functions), or for join logic (see sql-joins).
license: Free to use and adapt under CC BY 4.0, with credit to Afeez Adebayo. Link afeezadebayo.com, https://www.linkedin.com/in/afeez-adebayo/ and this skill's page at https://afeezadebayo.com/skills-md/data/sql-querying/conditional-aggregation/.
---

# Conditional aggregation

## When to use this, and when not to

Use this skill to compute several conditional metrics in one grouped query, or to turn rows into columns (a pivot). The triggers are a request to count or sum a subset without a separate query, to show categories side by side as columns, to build a pivot or cross-tab, or to replace several near-identical queries that differ only by a `WHERE` with one scan of the table. The technique is an aggregate applied only to the rows that match a condition, written with the `FILTER` clause or with `CASE` inside the aggregate.

Do not use it for a plain single-value `GROUP BY` total with no per-condition split (that is `grouping-aggregation`), for the general rules of how NULL behaves (`null-handling-in-sql`, which this skill depends on), for running totals and per-row ranking (`window-functions`), or for join logic (`sql-joins`). The defining feature here is that the condition sits inside the aggregate, so one grouped scan produces many columns, rather than one condition filtering the whole query.

The core idea: `SUM(amount) FILTER (WHERE status = 'paid')` adds up only the paid rows, and `SUM(CASE WHEN status = 'paid' THEN amount END)` means the same thing, because an aggregate ignores the NULLs that the non-matching rows produce (jOOQ, "The Performance Impact of SQL's FILTER Clause"). Get the `ELSE` right and the counts and averages are correct; get it wrong and they are quietly off.

## Inputs the agent must have before starting

1. The SQL engine and version. `FILTER` is native in PostgreSQL and SQLite but not in MySQL or SQL Server, which need the `CASE` form (see the appendix).
2. The row dimension: what each output row should represent (the `GROUP BY`, for example one row per month). If the result should be a single summary row, there is no `GROUP BY`.
3. The columns you want and whether that set is fixed and known (status values, quarters) or dynamic and changing. Fixed sets are hardcoded; dynamic sets need generated SQL or a BI layer.
4. The aggregate for each column: a sum of amounts, a count of rows, an average, or a single value per cell. The choice changes the `ELSE` handling.
5. What an empty cell should be: zero or NULL. A count or sum of no matching rows can be `0`, while an average of no rows is NULL.

If the set of columns is not fixed and could grow, confirm whether the result must be one flat table with a column per value (needs dynamic SQL) or whether a long format (one row per category) would serve, because a long result avoids the brittleness of hardcoded columns.

## The workflow, step by step

### 1. Confirm it is conditional aggregation, not a WHERE filter

If you want the whole result restricted to some rows, use `WHERE`. If you want several metrics that each cover a different subset of the same rows, side by side, use conditional aggregation so one scan produces them all. Decision rule: more than one condition in the same grouped result means conditional aggregation; a single condition on the entire query means `WHERE`.

### 2. Choose the aggregate by intent

- Sum of a value over matching rows: `SUM`.
- How many rows match: `COUNT`.
- Mean over matching rows: `AVG`.
- A single value per cell (a true pivot where each cell is one row's value): `MAX` or `MIN`, which also collapse accidental duplicates. Use `MAX(CASE WHEN ... THEN value END)`, not `SUM`, or repeated rows inflate the cell (Mimo, "How to Pivot in SQL").

### 3. Write it with FILTER, or CASE where FILTER is missing

Where the engine supports it, `FILTER` is clearer and slightly faster:

```sql
SELECT
  COUNT(*)                         AS orders,
  COUNT(*) FILTER (WHERE status = 'paid')     AS paid,
  SUM(amount) FILTER (WHERE status = 'paid')  AS paid_revenue
FROM orders;
```

Where it is not (MySQL, SQL Server), use `CASE` inside the aggregate. The implicit `ELSE NULL` makes non-matching rows ignored, because aggregates skip NULLs (modern-sql.com, "FILTER"):

```sql
SELECT
  COUNT(*)                                    AS orders,
  COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid,
  SUM(CASE WHEN status = 'paid' THEN amount END) AS paid_revenue
FROM orders;
```

On a measured PostgreSQL 15 test (sakila, 500 iterations, 5 runs), the `CASE` form was about 8% slower than `FILTER`; results vary by hardware and data, so treat it as a readability choice first (jOOQ, verify before relying on the figure).

### 4. Get the ELSE right: this is where counts and averages go wrong

This is the rule that separates correct conditional aggregation from subtly wrong output:

- **COUNT:** write `COUNT(CASE WHEN cond THEN 1 END)` with no `ELSE`. An `ELSE 0` is fatal, because `0` is non-null and `COUNT` counts non-null values, so it would count every row, matching or not. In MySQL, `COUNT(IF(cond, 1, NULL))` is the same idea (oneuptime, MySQL pivot).
- **AVG:** omit the `ELSE`. Writing `AVG(CASE WHEN cond THEN x ELSE 0 END)` pulls the mean down with zeros for every non-matching row; without the `ELSE`, non-matching rows are NULL and `AVG` ignores them (sql-quest2, "SQL CASE WHEN", which calls `ELSE 0` here "the wrong number").
- **SUM:** `ELSE 0` and no `ELSE` give the same total, because adding zero and ignoring a NULL both leave the sum unchanged. `ELSE 0` is only needed if you want the overall `SUM` to return `0` rather than NULL when no rows match (see `null-handling-in-sql`).

### 5. Pivot rows to columns

A pivot is one conditional aggregate per output column, plus a `GROUP BY` on the row dimension. One `CASE`-wrapped `SUM` (or `MAX`) per column you want:

```sql
SELECT
  product,
  SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS q2,
  SUM(CASE WHEN quarter = 'Q3' THEN amount ELSE 0 END) AS q3,
  SUM(CASE WHEN quarter = 'Q4' THEN amount ELSE 0 END) AS q4,
  SUM(amount)                                          AS total
FROM sales
GROUP BY product;
```

Without the `GROUP BY`, every row collapses into one giant pivot row (Mimo). Use `MAX(CASE WHEN col = 'x' THEN value END)` instead of `SUM` when each cell is a single value that could otherwise be double-counted.

### 6. Two-dimensional pivots

Nest the conditions to pivot on two dimensions at once:

```sql
SUM(CASE WHEN region = 'North' AND quarter = 'Q1' THEN revenue ELSE 0 END) AS north_q1
```

Each column is one combination (oneuptime). This grows quickly, so keep it to small, fixed dimension sets.

### 7. Decide fixed versus dynamic columns

Decision rule: if the set of values is stable and known (statuses, quarters, a handful of plan names), hardcode one aggregate per value; it is readable and safe. If the set changes often or is large, do not hand-maintain columns. Either return long format (one row per category with a `GROUP BY`) and pivot in the BI or application layer, or generate the column list dynamically. In MySQL, that is `GROUP_CONCAT(DISTINCT ...)` into a variable, then `PREPARE`/`EXECUTE`; raise `group_concat_max_len` first so the list is not truncated (oneuptime). Never interpolate untrusted input into that generated SQL.

### 8. Consider native PIVOT or crosstab

SQL Server has a `PIVOT` operator, and PostgreSQL has `crosstab` in the `tablefunc` extension. Both still need an explicit aggregate and a fixed list of output values, and `PIVOT` leaves a NULL in a column that has no matching rows (Microsoft Learn, "Using PIVOT and UNPIVOT"). They can be tidier for a straight pivot, but the `CASE` form is portable, handles two-dimensional and irregular cases, and is usually easier to read, so prefer it unless the native operator is clearly cleaner for a simple case.

### 9. Verify

Check that the row dimension is in `GROUP BY`, that every `COUNT(CASE ...)` has no `ELSE 0`, that every `AVG(CASE ...)` has no `ELSE`, that pivot columns reconcile (the pivoted columns sum to the unconditional total for each row), and that empty cells are the `0` or NULL you intended. For a pivot, run the equivalent long-format `GROUP BY` query and confirm the numbers match before trusting the wide one.

## Worked example, end to end

A team wants, per month, the number of orders, the number and revenue of paid orders, and the average value of paid orders. The first attempt counts wrongly:

```sql
SELECT month,
  COUNT(*) AS orders,
  COUNT(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid,       -- WRONG
  AVG(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS avg_paid  -- WRONG
FROM orders
GROUP BY month;
```

Both the `paid` count and the `avg_paid` are wrong for the same reason, the `ELSE 0`. The `COUNT` counts every row, because `0` is non-null, so `paid` equals `orders`. The `AVG` divides by all rows and includes a zero for every unpaid order, dragging the mean below the true average of paid orders. The fix drops the `ELSE` from both:

```sql
SELECT month,
  COUNT(*)                                        AS orders,
  COUNT(*) FILTER (WHERE status = 'paid')         AS paid,          -- or COUNT(CASE WHEN status='paid' THEN 1 END)
  SUM(amount) FILTER (WHERE status = 'paid')      AS paid_revenue,
  AVG(amount) FILTER (WHERE status = 'paid')      AS avg_paid
FROM orders
GROUP BY month
ORDER BY month;
```

On MySQL, the same result uses the `CASE` and `IF` forms: `COUNT(CASE WHEN status = 'paid' THEN 1 END)`, `SUM(CASE WHEN status = 'paid' THEN amount END)` and `AVG(CASE WHEN status = 'paid' THEN amount END)`. To confirm, a long-format check `SELECT month, status, COUNT(*), SUM(amount) FROM orders GROUP BY month, status` should reconcile with the pivoted columns row by row.

## Variations you will meet in practice

- **Percentages and rates.** A conditional count over the total gives a rate: `AVG(CASE WHEN status = 'paid' THEN 1.0 ELSE 0 END)` is the paid share (here `ELSE 0` is correct, because every row should count towards the denominator), or `COUNT(*) FILTER (WHERE status = 'paid') * 1.0 / COUNT(*)`.
- **Boolean flags.** `MAX(CASE WHEN event = 'login' THEN 1 ELSE 0 END)` per user answers "did this user ever log in", collapsing many rows to one flag.
- **First or last value per group.** `MAX(CASE WHEN rn = 1 THEN value END)` after numbering rows picks one value per group; for ongoing per-row work prefer `window-functions`.
- **COUNTIF-style helpers.** BigQuery has `COUNTIF(cond)`; elsewhere `COUNT(CASE WHEN cond THEN 1 END)` or `COUNT(*) FILTER (WHERE cond)` replicate it (modern-sql.com).
- **Pivoting a metric that can repeat.** When a cell could match several rows but should show one value, use `MAX`, not `SUM`, or the cell silently adds duplicates.

## Templates you can use verbatim

```sql
-- Conditional count, sum, average (FILTER: PostgreSQL, SQLite)
SELECT
  COUNT(*) FILTER (WHERE cond)      AS n_match,
  SUM(x)   FILTER (WHERE cond)      AS sum_match,
  AVG(x)   FILTER (WHERE cond)      AS avg_match
FROM t;

-- Portable CASE form (MySQL, SQL Server, anywhere)
SELECT
  COUNT(CASE WHEN cond THEN 1 END)     AS n_match,     -- no ELSE
  SUM(CASE WHEN cond THEN x END)       AS sum_match,   -- ELSE 0 optional
  AVG(CASE WHEN cond THEN x END)       AS avg_match    -- no ELSE
FROM t;

-- Pivot rows to columns (fixed value set)
SELECT dim,
  SUM(CASE WHEN k = 'a' THEN v ELSE 0 END) AS a,
  SUM(CASE WHEN k = 'b' THEN v ELSE 0 END) AS b,
  MAX(CASE WHEN k = 'c' THEN v END)        AS c_single
FROM t
GROUP BY dim;

-- Rate / share (ELSE 0 is correct: every row counts in the denominator)
SELECT AVG(CASE WHEN cond THEN 1.0 ELSE 0 END) AS share FROM t;
```

## Failure modes and anti-patterns

1. **`ELSE 0` inside a `COUNT`.** Spot it when a conditional count equals the total count, or every bucket has the same number. Fix: `COUNT(CASE WHEN cond THEN 1 END)` with no `ELSE`, or `COUNT(*) FILTER (WHERE cond)`.
2. **`ELSE 0` inside an `AVG`.** Spot it when an average looks too low and barely changes between groups. Fix: omit the `ELSE` so non-matching rows are NULL and ignored.
3. **Missing `GROUP BY`.** Spot it when a pivot returns a single row instead of one per dimension value. Fix: add the row dimension to `GROUP BY`.
4. **`SUM` where `MAX` was meant.** Spot it when a pivot cell is a multiple of the real value because the source has more than one row per cell. Fix: use `MAX(CASE WHEN ... THEN value END)` for single-value cells.
5. **Brittle or unsafe dynamic columns.** Spot it when a new category silently drops out of a hardcoded pivot, or a dynamic pivot breaks on a long or untrusted value list. Fix: for stable sets hardcode and review on change; for changing sets generate the list safely (raise `group_concat_max_len`, never interpolate untrusted input) or pivot in the BI layer.

## Quality bar

- Every `COUNT(CASE ...)` uses `THEN 1` with no `ELSE 0`; every `AVG(CASE ...)` omits the `ELSE`.
- The row dimension is in `GROUP BY`, and single-value pivot cells use `MAX`/`MIN`, not `SUM`.
- Pivoted columns reconcile with the unconditional total, confirmed against a long-format `GROUP BY` query.
- `FILTER` is used where the engine supports it, with the `CASE` form kept portable where it does not.
- Empty cells are the `0` or NULL intended, decided on purpose.
- Dynamic column lists, if any, are generated safely and never from untrusted input.

## Data handling and safety

Conditional aggregation reads data and returns a summary, so it is low risk on its own. The exception is dynamic pivots that build SQL from the data: never interpolate untrusted or user-supplied values into a `PREPARE`/`EXECUTE` statement or any generated query, because that is an injection path; allow-list the values or generate the column list only from trusted schema metadata, and raise `group_concat_max_len` so a truncated list does not silently drop columns. A conditional aggregate never changes data, but if its output drives a later bulk action (an update or export over the matched rows), treat that action as irreversible and require explicit human confirmation. When a pivot exposes small groups, be aware that a single-person cell can reveal an individual; aggregate to a safe minimum group size before sharing.

## Appendix: engine-specific notes (verify before relying on these)

- **PostgreSQL**: `FILTER (WHERE ...)` on any aggregate, including `array_agg` and ordered-set aggregates; PostgreSQL also allows a subquery inside the `FILTER` as an extension. For a flat cross-tab, the `tablefunc` extension provides `crosstab`. `count(*)` counts rows, aggregates otherwise ignore NULL.
- **MySQL and MariaDB**: no `FILTER` clause, so use `CASE` or the shorter `IF(cond, x, NULL)` inside the aggregate. Dynamic pivots are built with `GROUP_CONCAT(DISTINCT ...)` plus `PREPARE`/`EXECUTE`; set `group_concat_max_len` high enough first. No native `PIVOT` operator.
- **SQLite**: `FILTER (WHERE ...)` is supported on aggregates from 3.30; otherwise use the `CASE` form. No native `PIVOT`.
- **SQL Server**: no `FILTER` clause; use `CASE`, or the `PIVOT` operator, which needs an explicit aggregate and a fixed `IN (...)` list of values and leaves NULL in columns with no matching rows. `COUNT_BIG` for very large counts.
- **BigQuery**: `COUNTIF(cond)` is a built-in shorthand for `COUNT(CASE WHEN cond THEN 1 END)`; `CASE` aggregation also works.

## Sources

- jOOQ blog (Lukas Eder), "The Performance Impact of SQL's FILTER Clause": `FILTER` and the `aggregate(CASE WHEN cond THEN expr END)` form are provably equivalent; native `FILTER` support in PostgreSQL, SQLite and several other engines, with a `CASE` rewrite elsewhere; a measured ~8% `CASE` penalty on PostgreSQL 15 (sakila, 500 iterations, 5 runs), hardware-dependent. Verified 30 October 2026.
- modern-sql.com (Markus Winand), "FILTER": the `SUM(CASE WHEN cond THEN expr END)` fallback with implicit `ELSE NULL`, the `COUNT(CASE WHEN cond THEN 1 END)` form for counts, that `FILTER` is SQL:2003 optional feature T612 and thinly supported, and that BigQuery's `COUNTIF` is replicable with `CASE`. Verified 30 October 2026.
- PostgreSQL documentation, "Aggregate Expressions": `FILTER (WHERE ...)` feeds only matching rows to the aggregate; most aggregates ignore NULL; `count(*)` counts rows while `count(expr)` counts non-null values. Verified 30 October 2026.
- SQLite documentation, "Built-in Aggregate Functions": `FILTER (WHERE expr)` includes only rows where the expression is true; `count(X)` counts non-null values. Verified 30 October 2026.
- Microsoft Learn, "Use PIVOT and UNPIVOT": the `PIVOT` syntax with a required aggregate and a fixed `IN (...)` value list; a pivoted value with no matching rows returns NULL; NULLs are not considered when the aggregate is computed. Verified 30 October 2026.
- Mimo, "How to Pivot in SQL", and oneuptime, "MySQL pivot tables": the `SUM(CASE ... ELSE 0)` and `MAX(CASE ...)` pivot patterns, `COUNT(IF(cond,1,NULL))`, the `GROUP BY` requirement, two-dimensional pivots, and dynamic pivots via `GROUP_CONCAT` plus `PREPARE`/`EXECUTE`. Verified 30 October 2026.
- sql-quest2, "SQL CASE WHEN Tutorial": CASE returns the first matching branch; an unmatched CASE with no `ELSE` is NULL; conditional aggregation puts CASE inside aggregates; `ELSE 0` inside an `AVG` produces the wrong number. Verified 30 October 2026.