Why COUNT(*) Lies After a LEFT JOIN (and How to Count Correctly)

Reproducing the Bug

-- customers          -- orders
id | name             id  | customer_id | amount
---+-------           ----+-------------+-------
 1 | Alice            123 |           2 |  40.00
 2 | Bob              456 |           2 |  25.00

"How many orders does each customer have?" Alice has none, so we need a LEFT JOIN to keep her:

SELECT c.name, COUNT(*) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

name  | orders
------+-------
Alice |      1   -- wrong!
Bob   |      2

Alice shows 1 order. She has never ordered anything.

Why It Happens

Look at the joined rows before grouping:

name  | o.id | amount
------+------+-------
Alice | NULL |   NULL
Bob   |  123 |  40.00
Bob   |  456 |  25.00

The LEFT JOIN kept Alice by giving her one row with NULL in every order column. COUNT(*) doesn't care what's in a row; it counts that the row exists. So Alice's group has 1 row, and COUNT(*) returns 1.

COUNT(o.id) counts only rows where o.id is not NULL. Alice's only row has o.id = NULL, so she gets 0.

The Fix

Count a column from the right-hand table that is never NULL when a real match exists. The primary key is the safest choice:

SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

name  | orders
------+-------
Alice |      0
Bob   |      2

A good rule: after a LEFT JOIN, never use COUNT(*) to count things from the right-hand table. Use COUNT(right_table.primary_key).

Why the primary key? If you count a nullable column like o.coupon_code, real orders without a coupon won't be counted either. Primary keys can't be NULL, so the only NULLs come from missing matches.

COUNT(*) answers "how many rows are in this group?" COUNT(o.id) answers "how many orders are in this group?" After a LEFT JOIN, those are different questions.

Related Trap 1: SUM Returns NULL, Not 0

SELECT c.name, SUM(o.amount) AS total_spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

Alice gets NULL, not 0, because there are no non-NULL amounts to add. That NULL can break later calculations or show up as a blank in a dashboard. Wrap it:

SELECT c.name, COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

Related Trap 2: AVG Skips the Customers You Kept

"What's the average number of orders per customer?" There's no single column in the joined rows that answers this, and averaging an order column like AVG(o.amount) skips the NULL rows, so customers with no orders silently drop out. Aggregate per customer first, then average the result:

WITH per_customer AS (
  SELECT c.id, COUNT(o.id) AS orders
  FROM customers c
  LEFT JOIN orders o ON o.customer_id = c.id
  GROUP BY c.id
)
SELECT AVG(orders) AS avg_orders_per_customer
FROM per_customer;   -- (0 + 2) / 2 = 1.0

Related Trap 3: Two LEFT JOINs Multiply Each Other

Add a second one-to-many table, say support tickets, and counts can inflate:

SELECT c.name,
       COUNT(o.id) AS orders,
       COUNT(t.id) AS tickets
FROM customers c
LEFT JOIN orders  o ON o.customer_id = c.id
LEFT JOIN tickets t ON t.customer_id = c.id
GROUP BY c.name;

If Bob has 2 orders and 3 tickets, the JOIN produces 2 × 3 = 6 rows for him, and both counts come back as 6. Two quick fixes:

• Use COUNT(DISTINCT o.id) and COUNT(DISTINCT t.id). Fine for counts, but it doesn't fix SUM.
• Better: aggregate each table separately in its own CTE, then join the summaries, so each customer has at most one row from each side.

WITH o AS (SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS spent
           FROM orders GROUP BY customer_id),
     t AS (SELECT customer_id, COUNT(*) AS tickets
           FROM tickets GROUP BY customer_id)
SELECT c.name,
       COALESCE(o.orders, 0)  AS orders,
       COALESCE(o.spent, 0)   AS spent,
       COALESCE(t.tickets, 0) AS tickets
FROM customers c
LEFT JOIN o ON o.customer_id = c.id
LEFT JOIN t ON t.customer_id = c.id;

Notice that COUNT(*) is safe inside those CTEs, because there's no LEFT JOIN there: every row really is an order or a ticket.

Related Trap 4: Counting Only Some Orders

"How many paid orders does each customer have, including zero?" Putting WHERE o.status = 'paid' after the LEFT JOIN removes Alice entirely, because her NULL status fails the filter. Two correct options:

-- Option A: move the condition into ON
SELECT c.name, COUNT(o.id) AS paid_orders
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id AND o.status = 'paid'
GROUP BY c.name;

-- Option B: conditional aggregation
SELECT c.name,
       COUNT(CASE WHEN o.status = 'paid' THEN o.id END) AS paid_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

Option B is handy when you want several counts at once, like paid, refunded and pending, in a single query. PostgreSQL also supports the shorter COUNT(o.id) FILTER (WHERE o.status = 'paid').

Checklist for "Count Per X" Queries

• Need rows with zero? Use LEFT JOIN, with the "per X" table on the left.
• Count the right table's primary key, not *.
• Wrap SUMs in COALESCE(..., 0).
• More than one one-to-many JOIN? Aggregate each table first.
• Filtering the right table? Put the condition in ON, or use conditional aggregation.

Catch It in the Wild

It's easy to hit this bug when counting evidence per suspect: the suspect with no records looks like they have one. Try it yourself in Murder in Blackwater Bay, and see whether your counts hold up.

• Previous: why JOINs are hard, and how to make them click
• Start Learning Mode