DISTINCT, and counting things correctly

Counting looks like the easiest thing in SQL and it produces more wrong numbers than anything else. This lesson is the four ways it goes wrong, each of which returns a plausible figure and no error.

1. The join that multiplies your rows

The worst one, because the number looks reasonable.

The library has 40 books and 95 copies:

SELECT sum(copies) AS right_total FROM books;

 right_total
-------------
          95

Each book belongs to one to three categories, so book_categories has 83 rows. Now join:

SELECT count(*) AS rows_after_join
FROM books b JOIN book_categories bc ON bc.book_id = b.id;

 rows_after_join
-----------------
              83

Forty books became 83 rows, because a book in three categories appears three times. Sum anything from books now and you count it once per category:

SELECT sum(b.copies) AS wrong_total
FROM books b JOIN book_categories bc ON bc.book_id = b.id;

 wrong_total
-------------
         202

202 instead of 95. More than double, no error, and if you did not already know the answer you would have no reason to doubt it.

This is fan-out, and it happens whenever you join to a table on the many side of a one-to-many. It gets worse with two such joins: join books to categories and to loans and you multiply both ways, producing numbers that are wildly wrong.

Three fixes

-- 1. count distinct — easy, and slow on large data
SELECT count(DISTINCT b.id),    -- 40, correct
       sum(DISTINCT b.copies)   -- 21, and WRONG: see below
FROM books b JOIN book_categories bc ON bc.book_id = b.id;

-- 2. aggregate BEFORE joining — usually the right answer
SELECT b.title, c.n_categories
FROM books b
JOIN (SELECT book_id, count(*) AS n_categories FROM book_categories GROUP BY book_id) c
  ON c.book_id = b.id;

-- 3. don't join at all — use a correlated subquery or EXISTS
SELECT b.title,
       (SELECT count(*) FROM book_categories bc WHERE bc.book_id = b.id) AS n_categories
FROM books b;

count(DISTINCT b.id) gives 40 and is correct. sum(DISTINCT b.copies) gives 21 and is nonsense — DISTINCT removes duplicate values, so the distinct copy-counts across the whole library are 1, 2, 3, 4, 5, 6 and those add to 21. Two different books with 3 copies each contribute 3 once.

Three numbers for one question: 202 (wrong, fanned out), 21 (wrong, differently) and 95 (right). The fix for counting is not the fix for summing, and that is the trap.

Aggregate before you join. It is the habit that prevents this whole family of bugs, and module 5 returns to it.

2. Counting the wrong side of a LEFT JOIN

You will meet LEFT JOIN properly in module 5; the counting mistake belongs here.

-- WRONG: members with no loans get a count of 1
SELECT m.name, count(*) AS loans
FROM members m LEFT JOIN loans l ON l.member_id = m.id
GROUP BY m.id, m.name;

A LEFT JOIN keeps the member and fills the loan columns with NULL. count(*) counts rows, and there is one row — so a member with no loans reports 1 loan.

-- right
SELECT m.name, count(l.id) AS loans
FROM members m LEFT JOIN loans l ON l.member_id = m.id
GROUP BY m.id, m.name;

count(l.id) counts non-NULL values, and the phantom row has l.id as NULL, so it counts 0.

With a LEFT JOIN, count a column from the right-hand table, never *. That single rule prevents a whole class of off-by-one reports.

3. NULL quietly shrinking the denominator

From the first lesson of this module, restated because it is a counting error:

SELECT count(*) AS all_loans, count(fine_paise) AS with_a_fine_value FROM loans;

 all_loans | with_a_fine_value
-----------+-------------------
       180 |               136

Any avg, sum or count on fine_paise is over 136 rows. If somebody asks "what is the average fine?" the honest answer includes "across the 136 returned loans".

Report the denominator. A number without one is not an answer, it is a number.

4. DISTINCT hiding the real problem

SELECT DISTINCT b.title FROM books b JOIN book_categories bc ON bc.book_id = b.id;

That gives 40 titles, correctly. And it is worth asking why there were duplicates — the join produced 83 rows and DISTINCT threw 43 away.

Here it is harmless. But SELECT DISTINCT added to make a result look right is one of the most reliable signs that a query is wrong in a way you have not found yet. Duplicates are a symptom; DISTINCT treats the symptom.

Ask first: should this join be there at all? Should it be EXISTS? Should the aggregation happen before the join?

Counting distinct things properly

SELECT count(*)                  AS loan_rows,
       count(DISTINCT member_id) AS distinct_members,
       count(DISTINCT book_id)   AS distinct_books
FROM loans;

 loan_rows | distinct_members | distinct_books
-----------+------------------+----------------
       180 |               28 |             39
(1 row)

Three genuinely different questions. Name them in your output — loan_rows, not count. A column called count in a report is how two different numbers get confused a week later.

count(DISTINCT ...) is expensive: the database must remember every value it has seen. On millions of rows it is slow and memory-hungry, and approximate alternatives exist (HyperLogLog via the postgresql-hll extension) when "about 4.2 million" is good enough.

Counting rows in a huge table

SELECT count(*) FROM loans;

PostgreSQL has to read every row to answer that exactly, because of how its concurrency works — different transactions can legitimately see different row counts. On a table of hundreds of millions, that is a slow query.

For an estimate:

SELECT reltuples::bigint AS estimate FROM pg_class WHERE relname = 'loans';

Maintained by the statistics collector, instant, and approximate. Good enough for "about how many rows" and never for anything financial.

The checklist

Before trusting a count:

□  Did a join multiply the rows?          Compare count(*) with and without it.
□  Is there a LEFT JOIN?                  Count a right-hand column, not *.
□  Could the column be NULL?              count(*) and count(col) will differ.
□  Did I add DISTINCT to fix something?   Find out what.
□  What is the denominator?               Say it in the output.
□  Do the numbers add up?                 Check a small case by hand.

That last line is the most valuable. Take five rows you can count by hand and run the query against them. Every error above is obvious at five rows and invisible at fifty thousand.

Check your work

What fan-out is. A join to the many side repeats the left row, so sums multiply.

The numbers here. 40 books, 95 copies — joined to categories, sum(copies) gives 202.

Three fixes. count(DISTINCT), aggregate before joining, or do not join.

Why sum(DISTINCT x) is usually wrong. It removes duplicate values, not duplicate rows.

Why a LEFT JOIN with count(*) gives 1 instead of 0. There is a row; the right-hand columns are NULL.

The rule for counting with a LEFT JOIN. Count a right-hand column.

What to report alongside an average. Its denominator.

What DISTINCT usually indicates. A join that should not be there, or aggregation that should have happened first.

Why count(DISTINCT) is expensive. It must remember every value seen.

Why exact count(*) is slow on a huge table. Every row must be read, because transactions can see different counts.

Where to get an estimate. reltuples in pg_class — never for anything financial.

The most valuable check. Run it against five rows you can count by hand.

Practice

1. Compute sum(copies) on books. Then join to book_categories and compute it again. Explain the difference.
2. Count the rows before and after that join.
3. Fix the sum three ways: count(DISTINCT), a pre-aggregated subquery, and a correlated subquery.
4. Try sum(DISTINCT copies) and explain why it is wrong.
5. Every member in the seed data has borrowed something, so make one who has not: INSERT INTO members (name, joined) VALUES ('Nobody Here', current_date); Then write the LEFT JOIN member-loan count with count(*) and find their number.
6. Change it to count(l.id) and confirm it becomes 0.
7. Compare count(*) and count(fine_paise) on loans. Write the sentence you would put in a report.
8. Add DISTINCT to a query to remove duplicates, then work out where the duplicates came from and remove the cause instead.
9. Count loan rows, distinct members and distinct books in one query. Name all three columns meaningfully.
10. Get the row estimate for loans from pg_class and compare it with the exact count.
11. Build a five-row version of the fan-out example by hand and verify the wrong number yourself.
12. Take any counting query you have written before and run the six-point checklist over it.

Official documentation

• PostgreSQL — Aggregate functions — count, count(DISTINCT) and the NULL behaviour.
• PostgreSQL — Joins — Why a join to the many side multiplies rows.
• PostgreSQL — pg_class — reltuples, and how current it is.
• PostgreSQL — Row estimation examples — How the planner's estimates are produced, which module 7 builds on.

---

You can now turn many rows into one number, per group, filtered before and after — and, more importantly, tell when that number is wrong.

Next module: designing the tables in the first place.