NULLs and Aggregation

Cheatsheet: NULLs and Aggregation

One page for Topic 2. NULL means "unknown" — not zero, not empty string, not false. That one sentence explains every behavior below.

Three-valued logic: how NULL behaves

NULL vs 0 is the most consequential choice for business metrics. If 40% of orders store "no discount" as NULL, AVG(discount_pct) averages only the discounted orders and looks far higher than the true all-orders average. Store 0 instead, and the identical data yields a much lower number.

The COUNT family

Fast data-quality check — run both side by side:

SELECT COUNT(*) AS total_rows, COUNT(email) AS rows_with_email
FROM signups

If the numbers differ, some signups have no email — COUNT(*) hides that, COUNT(email) reveals it. And when a dashboard claims "10,000 users have email addresses," verify the query used COUNT(email), not COUNT(*) on a table that happens to be about users.

COALESCE: making the NULL choice explicit

COALESCE(col, fallback) returns the first non-NULL argument — the standard way to treat missing as zero on purpose:

SELECT AVG(COALESCE(discount_pct, 0)) AS avg_discount_all_orders
FROM orders

Now every order counts in the average. Whether that's right depends on what the metric claims to measure — the point is the choice is now visible in the code instead of hidden in storage conventions.

GROUP BY: the grain rule

The GROUP BY clause answers "one row per what?" — that's the grain, and it determines what every number means.

Rules:

• Every column in SELECT that isn't inside an aggregate (SUM, COUNT, AVG...) must appear in GROUP BY. Some databases allow violations and silently pick an arbitrary row's value — essentially random per group.
• When two metrics that "should match" don't, check the grain first. One query grouped by month, the other by week — both individually correct, answering different questions.
• A GROUP BY finer than the question asked (region + month + product when asked for region + month) answers a narrower question, even though every number is correct at that grain.

HAVING vs WHERE

SELECT region, SUM(revenue) AS total_revenue
FROM sales
WHERE order_date >= '2026-01-01'   -- drop old rows first
GROUP BY region
HAVING SUM(revenue) > 100000       -- then drop small regions

They run at different stages and cannot be swapped.

Traps

• = NULL or != NULL anywhere in a WHERE clause — that condition returns zero rows every time, with no error or warning.
• AVG()/SUM() on a column with unstated NULLs — ask whether missing values are stored as NULL or 0; the two give different answers from identical data.
• != used to mean "everything except X" — it silently drops the unknowns too.
• COUNT(*) reported as if it were COUNT(some_column) — verify which one the query actually ran.
• A non-aggregated SELECT column missing from GROUP BY — arbitrary value per group on lenient databases.
• Comparing metrics from two queries without checking they GROUP BY the same grain.
• Aggregate condition placed in WHERE instead of HAVING — error at best, wrong result at worst.