0Pricing
SQL Academy · Lesson

Common Pitfalls: NULLs in Aggregates

Avoid the classic NULL traps: AVG ignoring NULLs, COUNT(column) skipping NULLs, and surprises when grouping on nullable columns.

Common Pitfalls: NULLs in Aggregates is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Aggregates Skip NULL

Every aggregate except COUNT(*) ignores NULL inputs. This is occasionally what you want — and sometimes a bug.

Classic Trap: COUNT(*) vs COUNT(col)

Different results when the column has NULLs:

SELECT COUNT(*)         FROM users;      -- 1000
SELECT COUNT(email)     FROM users;      -- 950  (50 users have NULL email)

Bug: AVG Doesn't Count NULL as Zero

Ratings [5, 5, NULL] → AVG = 5, not (5+5+0)/3 = 3.33. Decide if that's what you want.

SELECT AVG(rating) FROM reviews;        -- 5
SELECT AVG(COALESCE(rating, 0)) FROM reviews;   -- 3.33

Empty Table = NULL Aggregate

If no rows match, SUM/AVG/MIN/MAX return NULL, not 0. Handle that with COALESCE:

SELECT COALESCE(SUM(total), 0) AS revenue
FROM orders
WHERE user_id = 999999;       -- safe even when user has no orders

SUM of NULL Column

If every row's value is NULL, SUM returns NULL — not 0. Same gotcha.

GROUP BY with NULL Grouping Key

NULL forms its own group:

SELECT country, COUNT(*) FROM users
GROUP BY country;
-- → ('US', 100), ('CA', 30), (NULL, 5)

Counting Distinct With NULLs

COUNT(DISTINCT col) ignores NULLs:

INSERT INTO t (c) VALUES (1),(2),(NULL),(NULL),(2);
SELECT COUNT(DISTINCT c) FROM t;   -- 2 (NULL not counted)

Multi-Column DISTINCT

A tuple containing NULL is still distinct from another with the same non-NULL columns:

SELECT COUNT(*) FROM (
  SELECT DISTINCT a, b FROM t
) sub;

Beware Implicit Joins to Optional Tables

A LEFT JOIN can introduce NULLs that change your aggregate. Decide whether to count those rows or filter them out:

SELECT u.id, COUNT(o.id) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;          -- COUNT(o.id) gives 0 for users with no orders

FILTER for Conditional Counts

FILTER beats CASE-inside-SUM for readability:

SELECT COUNT(*) FILTER (WHERE status = 'paid') AS paid_count,
       COUNT(*) FILTER (WHERE status = 'refunded') AS refund_count
FROM orders;

Always Test Aggregates with NULLs

When adding a new aggregate query, run it against a sample that includes NULLs. The bug almost never shows up in clean test data.

Recap

NULL + aggregate is the most common SQL bug.

  • Aggregates skip NULLs (except COUNT(*))
  • Empty result → NULL, not 0
  • Wrap with COALESCE when 0 is wanted
  • Watch LEFT JOIN aggregates carefully

Quick Check

Reviews has ratings [5, 5, NULL, 1]. What does AVG(rating) return?

Frequently asked questions

Is the “Common Pitfalls: NULLs in Aggregates” lesson free?

Yes — the full text of “Common Pitfalls: NULLs in Aggregates” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Common Pitfalls: NULLs in Aggregates”?

Avoid the classic NULL traps: AVG ignoring NULLs, COUNT(column) skipping NULLs, and surprises when grouping on nullable columns. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Common Pitfalls: NULLs in Aggregates” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. COUNT, SUM, AVG, MIN, MAX
  2. GROUP BY Single and Multiple Columns
  3. HAVING vs WHERE
  4. Common Pitfalls: NULLs in Aggregates
← Back to SQL Academy