Why does putting the condition in WHERE instead of inside SUM(CASE …) drop zero-count groups?

## Two places to put the same predicate These queries look like they answer the same question, and do not: ```sql -- A: filter the rows SELECT customer_id, COUNT(*) AS cancelled FROM orders WHERE status = 'cancelled' GROUP BY customer_id; -- B: filter inside the aggregate SELECT customer_id, SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled FROM orders GROUP BY customer_id; ``` Query A returns only customers who have at least one cancelled order. Query B returns every customer who has any order at all, with a 0 where A had nothing. Neither is wrong; they answer different questions, and picking the wrong one is a bug that never raises an error. ## Why the rows vanish `WHERE` is a row filter applied before rows are collected into groups. A group comes into existence because at least one row carries its key — no surviving rows, no key, no group, no output line. So a customer whose every order is `'shipped'` is not merely counted as zero; the customer is absent from the result set entirely. The report looks complete because nothing signals the gap, and the missing rows are exactly the ones an analyst most wanted to see: the customers with none. ## Denominators, not just presence The damage is not limited to disappearing rows. After a `WHERE` filter, every other aggregate in the query also sees only the filtered rows: ```sql -- broken: cancel_pct is 100.0 for every surviving customer SELECT customer_id, 100.0 * COUNT(*) / COUNT(*) AS cancel_pct FROM orders WHERE status = 'cancelled' GROUP BY customer_id; ``` `COUNT(*)` was meant to be "all of this customer's orders", and after the filter it is "this customer's cancelled orders". Any mixed query — a subset measure beside a total, two different subsets side by side — needs the conditions inside the aggregates precisely because there is only one `WHERE` clause and several different subsets to measure. ## HAVING does not rescue it A common wrong fix is to move the predicate to `HAVING`. `HAVING` filters groups after they are built and after their aggregates are computed; it can drop groups, never resurrect ones that were never formed. Writing `HAVING status = 'cancelled'` is also usually illegal, because `status` is neither a grouping column nor inside an aggregate. `HAVING` is for predicates *about the group* — `HAVING SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) = 0`, meaning "customers who never cancelled", which is a genuinely useful pairing with conditional aggregation and is impossible to express with `WHERE`. ## Groups that never existed at all Conditional aggregation restores customers whose orders were all non-matching. It cannot restore customers with **no orders at all**, because those customers contribute no rows to `orders` in the first place. If the report must list every customer, the query has to start from the customer table: ```sql SELECT c.customer_id, SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id GROUP BY c.customer_id; ``` Here the `ELSE 0` is doing real work: a customer with no orders gets one all-NULL joined row, the `CASE` falls to `ELSE`, and the sum is 0 rather than NULL. Note also that a predicate on the right-hand table of a `LEFT JOIN` must go in the `ON` clause or inside the `CASE`; putting it in `WHERE` discards the unmatched rows and turns the outer join back into an inner one — the same disappearing act, one level down. ## When WHERE is still the right answer Use `WHERE` when the non-matching rows are genuinely irrelevant to every measure in the query: a report of cancelled-order volume by month has no use for shipped orders, and filtering early means the grouping step handles fewer rows. Use conditional aggregation when the same pass must produce several different subsets, when zero must be visible, or when a total is needed alongside a subset. The two also compose: a `WHERE` that narrows to the reporting period, with `CASE` conditions inside the aggregates that split that period by status. ## Spotting it in review The symptoms are recognisable. A report whose row count shrinks when a filter is added that "should only affect one column". A rate column that is 100% everywhere. A dashboard where an entity disappears rather than showing zero, and reappears the moment it records its first matching event. In each case the question to ask is: does this predicate decide which rows *exist*, or only what they *contribute*?