Should COALESCE go inside AVG(rating) or around it, and what changes?

## Two NULLs, two guards There are two distinct NULLs in an aggregate query, and `COALESCE` addresses whichever one it is nearest to. - **A row-value NULL**: the column is NULL on a row that exists. Aggregates eliminate these before computing, so the row contributes nothing and does not appear in `AVG`'s denominator. `COALESCE` placed *inside* the aggregate intercepts this NULL and substitutes a value, which puts the row back into the computation. - **An aggregate-result NULL**: the aggregate had no non-NULL input at all — no rows matched, or every value was NULL — so it returned NULL. `COALESCE` placed *outside* intercepts this one and gives the caller a scalar default. They are not two spellings of one idea. Each leaves the other case untouched. ## The numbers ```sql -- reviews: 5 rows; ratings 5, 4, 3 and two NULLs SELECT AVG(rating) AS avg_of_rated, -- 4.0 (12 / 3) AVG(COALESCE(rating, 0)) AS missing_as_zero, -- 2.4 (12 / 5) COALESCE(AVG(rating), 0) AS zero_if_no_data, -- 4.0 (guard never fires) COUNT(*) AS rows_total, -- 5 COUNT(rating) AS rows_rated -- 3 FROM reviews; ``` The inner form moved the answer by 1.6 stars without any data changing — it changed the *definition* of the metric. The outer form changed nothing here, because there was data; it would only have mattered had the table been empty. ## Choosing between them Ask what NULL means in this column. **If NULL means "unknown" or "not yet supplied"** — the usual case for a rating — the inner form is a data-fabrication bug. Its worst property is that the metric becomes a function of reporting completeness: a product whose reviewers mostly leave the score blank looks terrible, and the number improves whenever collection improves, even if satisfaction is flat. It also makes the measure non-comparable across periods and across segments with different response rates. Average the rated rows and disclose `COUNT(rating)` alongside so the reader can judge how much weight the figure deserves. **If NULL means "zero by convention"** — a `late_fee` column left NULL when there was no fee, a `discount` that is NULL when none applied — then the missing rows genuinely belong in the denominator and the inner form is correct. In that situation, prefer fixing the model: `DEFAULT 0` plus `NOT NULL` removes the ambiguity permanently and takes an entire class of bug off the table. Leaving a column nullable when NULL and 0 mean the same thing guarantees that some future query will disagree with this one. **The outer guard is a separate decision** about the caller's contract. Use it when a NULL scalar would break the consumer — arithmetic that would propagate the NULL, a driver mapping to a non-nullable numeric type, a JSON field that must be a number. Do not use it when zero would be a false statement to the reader; for averages it usually is. Returning `COUNT(*)` next to the measure lets the consumer distinguish "no data" from a real zero without you having to encode that distinction in the value itself. ## The combined form and why it is rarely right `COALESCE(AVG(COALESCE(rating, 0)), 0)` is legal and occasionally seen. It says "missing ratings score zero, and if there are no rows at all report zero" — a strong claim, and in this shape the inner guard already makes the outer one nearly unreachable: with at least one row, the average of coalesced values is never NULL. If you find yourself writing it, that is a sign the metric definition has not been decided. ## Diagnosing the disagreement in the wild When two dashboards report different averages for "the same" number, the usual culprits are exactly these two placements, plus a third: a `WHERE rating IS NOT NULL` filter, which matches `AVG(rating)` for that column but silently changes every other measure in the same query — `COUNT(*)`, sums over other columns, ratios. A quick reconciliation query showing `COUNT(*)`, `COUNT(rating)`, `AVG(rating)` and `AVG(COALESCE(rating, 0))` side by side pins down which definition each dashboard implemented, and the gap between the two counts tells you how much the choice is worth. ## The senior answer in one breath "Inside `COALESCE` changes what the metric means — unrated rows become zeros in both numerator and denominator. Outside `COALESCE` only protects the caller from a NULL when there was no data. For a rating, I average the rated rows and publish the count with it; I would only push the zero inside if NULL genuinely meant zero, and in that case I would make the column `NOT NULL DEFAULT 0` so nobody has to decide again."