Why does SUM(amount) return NULL instead of 0 when no rows match the WHERE clause?

## Empty input, not missing data SQL's aggregates operate on a multiset of input values. Before computing, every aggregate except `COUNT(*)` removes the NULLs from that multiset. Two situations then leave the aggregate with **nothing to work on**: 1. No row satisfied the `WHERE` clause (or the table is empty), so the input was empty to begin with. 2. Rows matched, but the aggregated column was NULL in every one of them, so NULL elimination emptied it. The standard specifies the same outcome for both: `SUM`, `AVG`, `MIN` and `MAX` over an empty input return **NULL**. `COUNT` is the deliberate exception — counting an empty set is 0, an exact and meaningful answer. The design reasoning is worth being able to state. `SUM` returning 0 would assert "the total is zero", which is a claim about data that does not exist. NULL asserts "there is no total to report". Those are genuinely different facts: a customer with no payments at all is not the same as a customer whose payments net to zero, and a report that conflates them is lying quietly. ## The failure it causes ```sql SELECT SUM(amount) AS total -- NULL when the customer has no 2026 payments FROM payments WHERE customer_id = 42 AND paid_at >= DATE '2026-01-01'; ``` That NULL then travels. In SQL, NULL propagates through arithmetic, so `credit_limit - SUM(amount)` is NULL rather than `credit_limit`. In application code, a driver hands back a null reference where the caller expected a number — a `NullPointerException` in Java, a `None` in Python arithmetic, a non-nullable-decimal mapping error. The bug typically appears only for the new customer, the empty date range, or the first day of the month, which is why it survives testing. ## The fix, and where it goes ```sql SELECT COALESCE(SUM(amount), 0) AS total FROM payments WHERE customer_id = 42 AND paid_at >= DATE '2026-01-01'; ``` `COALESCE` returns its first non-NULL argument, so this yields the real total when rows exist and 0 when they do not. The placement matters and interviewers probe it: **`SUM(COALESCE(amount, 0))` does not fix this case.** That form protects against NULLs *inside* rows — it converts a NULL `amount` on an existing row into 0 — but when there are no rows at all there is nothing for the inner `COALESCE` to act on, and `SUM` still returns NULL. The guard has to sit outside the aggregate because the NULL is produced by the aggregate. The same guard applies to `AVG`, `MIN` and `MAX`, though for `AVG` a 0 default is usually the wrong choice: "average of nothing" is rarely "zero average", and passing the NULL through so the presentation layer can render an em dash is often more honest. ## When you should *not* coalesce Suppressing the NULL destroys information. If downstream logic must distinguish "no activity" from "activity that nets to zero", keep the NULL, or return both measures: ```sql SELECT COUNT(*) AS payment_count, -- 0 tells you there were no rows COALESCE(SUM(amount), 0) AS total FROM payments WHERE customer_id = 42; ``` `COUNT(*)` is the disambiguator, since it returns 0 rather than NULL on empty input. This pairing — a count next to a coalesced sum — is the idiom experienced authors reach for in reporting queries. ## The related shape that catches people A scalar subquery used inside a larger expression has the same hazard: ```sql SELECT c.id, c.credit_limit - COALESCE((SELECT SUM(p.amount) FROM payments p WHERE p.customer_id = c.id), 0) AS remaining FROM customers c; ``` Without the `COALESCE`, every customer with no payments gets a NULL `remaining` instead of their full limit — the arithmetic, not the aggregate, is what finally surfaces the problem. ## What a strong answer sounds like "`SUM` over an empty set is NULL by definition — zero is a total, and there was no total. `COUNT` is the exception because counting nothing really is zero. I wrap it in `COALESCE(SUM(x), 0)` at the outer level, not `SUM(COALESCE(x, 0))`, since that inner form only helps when rows exist. And I only coalesce when zero is a truthful default; otherwise I return `COUNT(*)` alongside so the caller can tell empty from zero."