Aggregate Functions

Aggregate functions collapse many rows into one — and NULL changes the count

COUNT, SUM, AVG, MIN, MAX reduce a set of rows to a single value (per group, when used with GROUP BY). The one thing you must get right is how they treat NULL, because it silently changes results.

• COUNT(*) counts rows — every row, including ones full of NULLs and duplicates.
• COUNT(col) counts non-NULL values of col — NULLs are skipped.
• COUNT(DISTINCT col) counts distinct non-NULL values.
• SUM, AVG, MIN, MAX all ignore NULLs.

The AVG trap, traced

Column bonus = [100, 200, NULL, 300, NULL]:

• COUNT(*) = 5 but COUNT(bonus) = 3.
• SUM(bonus) = 600; AVG(bonus) = 600 / 3 = 200 — the denominator is the count of non-NULL values, not the row count. If your intent is "treat missing bonus as 0", you must say so: AVG(COALESCE(bonus, 0)) = 600 / 5 = 120. The two answers (200 vs 120) are both "the average" — the difference is entirely about how NULL is interpreted.

Pitfalls

• Using COUNT(col) when you meant rows (or vice-versa) — they differ exactly by the number of NULLs.
• Aggregates other than COUNT(*) over an all-NULL group return NULL, not 0.
• Any non-aggregated column in the SELECT must appear in GROUP BY (or you get an error / a nondeterministic value under MySQL's relaxed mode).

Takeaways

• COUNT(*) = rows; COUNT(col) = non-NULL values; they differ by the NULL count.
• AVG = SUM / (non-NULL count). Use COALESCE if NULLs should count as 0.
• SUM/AVG/MIN/MAX skip NULLs; an all-NULL group aggregates to NULL.

---

Re-authored for correctness for this guide (the prior version conflated COUNT(col) with COUNT(*)). Per the SQL standard & MySQL aggregate docs. See also: Handle NULLs in SQL, GROUP BY, HAVING.

🎯 STRICT STANDOUT: Why / worked / when-not / failure / drills — Aggregate Functions

Why this concept exists (judgment chain)

Aggregates collapse bags of values; NULL policy is the entire correctness story. COUNT(*) counts rows; COUNT(col) skips NULL; AVG uses non-NULL denominator only. Choosing COALESCE vs raw AVG is a product decision ("missing bonus is 0" vs "unknown bonuses ignored").

Worked example with numbers or traced steps

bonus = [100, 200, NULL, 300, NULL]
COUNT(*) = 5
COUNT(bonus) = 3
SUM(bonus) = 600
AVG(bonus) = 600/3 = 200
AVG(COALESCE(bonus,0)) = 600/5 = 120
All-NULL group: SUM/AVG/MIN/MAX → NULL, not 0.
COUNT(DISTINCT bonus) = 3.

When NOT to use / named alternative

Do not COALESCE to 0 for averages when NULL means "not applicable" (e.g. optional survey score) — that biases the mean down. Prefer COUNT(*) for "how many orders"; COUNT(shipped_at) for "how many already shipped". Avoid non-aggregated SELECT columns outside GROUP BY.

Failure / ops fingerprint

Dashboard regression after introducing nullable bonus: averages jump or drop without app-layer code change. MySQL ONLY_FULL_GROUP_BY off returns nondeterministic extra columns. Ops: compare COUNT(*) vs COUNT(col) in data quality monitors to track NULL rate.

Hostile-panel Q&As (model answers)

Q1. Why is AVG not SUM/COUNT(*)?
Model answer: Standard AVG divides by the count of non-NULL inputs, matching SUM's ignore-NULL rule.

Q2. COUNT(*) on a row of all NULLs?
Model answer: Still 1 — the row exists. COUNT(col) is 0 for that row's contribution.

Q3. Empty table AVG?
Model answer: AVG of empty set is NULL (no rows), not 0 — handle in app with COALESCE if UI wants 0.

I just read a lesson on **Aggregate Functions** (Databases) and want to truly understand it. Explain Aggregate Functions from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **Aggregate Functions** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **Aggregate Functions** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **Aggregate Functions** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.