When joins multiply rows

Why totals come out too high after a join, how to spot it, and the aggregate-first pattern that fixes it.

This is the most common way a report ends up with a wrong total that nobody notices. Every query in it runs, and every number looks plausible. They’re just too big.

The setup

You want each account’s deal total and its number of contacts in one report. The obvious query:

SELECT a.name,
       SUM(o.amount) AS total_amount,
       COUNT(c.contact_id) AS contacts
FROM accounts AS a
JOIN opportunities AS o ON o.account_id = a.account_id
JOIN contacts      AS c ON c.account_id = a.account_id
WHERE a.account_id = 1
GROUP BY a.name;

Northwind’s real deal total is $465,000, and it has 4 contacts. The query says $1,860,000 and 56.

What happened

Lesson 14 showed that a join returns one row per matching pair. Northwind has 14 deals and 4 contacts. Both join on account_id, so every deal pairs with every contact:

14 deals × 4 contacts = 56 rows

Each deal’s amount appears 4 times, once per contact, so the total is 4 times too big. Each contact appears 14 times. This is called fan-out: one row on one side fanning out into many rows on the other.

It happens whenever you join two separate one-to-many relationships to the same table. Deals and contacts both hang off accounts, but they have nothing to do with each other.

The word for this: grain

The grain of a table or result is what one row represents. accounts is one row per account. opportunities is one row per deal. The fan-out query above is one row per deal and contact pair, which isn’t something anyone asked for.

Before writing a join, say the grain of each side out loud, and the grain you want at the end. If two sides are both “many” for the same key, you’ve found a fan-out.

How to spot it

Compare the row count with a count of unique IDs (lesson 14’s check):

SELECT COUNT(*)                         AS rows_after_join,
       COUNT(DISTINCT o.opportunity_id) AS unique_deals
FROM opportunities AS o
JOIN contacts AS c ON c.account_id = o.account_id;

That gives 193 rows for only 64 deals. If the two numbers differ, rows multiplied.

The fix: aggregate first, then join

Shrink each side to one row per account before joining. Then every join is one row to one row, and nothing multiplies:

WITH deal_totals AS (
    SELECT account_id, SUM(amount) AS total_amount
    FROM opportunities
    GROUP BY account_id
),
contact_counts AS (
    SELECT account_id, COUNT(*) AS contacts
    FROM contacts
    GROUP BY account_id
)
SELECT a.name, d.total_amount, c.contacts
FROM accounts AS a
JOIN deal_totals    AS d ON d.account_id = a.account_id
JOIN contact_counts AS c ON c.account_id = a.account_id
WHERE a.account_id = 1;

Northwind now shows $465,000 and 4 contacts. This is lesson 24’s CTE pattern doing its most important job.

Why DISTINCT isn’t the fix

COUNT(DISTINCT c.contact_id) would give the right contact count. There’s no equivalent for the amount, though. SUM(DISTINCT o.amount) adds up unique amounts, so two different $15,000 deals count once. It’s wrong in a different way, and harder to spot. Fix the grain instead.

Common mistakes

Dialect notes

Fan-out isn’t a dialect issue. Every database does exactly what the join says, and none of them warn you. The aggregate-first fix works everywhere that supports CTEs, or with subqueries in FROM (lesson 23) on older MySQL versions.

Snowflake notes

On a warehouse like Snowflake, an accidental fan-out also costs money: a join that multiplies millions of rows burns compute while it runs. The Query Profile shows how many rows each join produced, so a join step that outputs far more rows than went in is a fan-out. In dbt, the unique test on a model’s key column catches fan-out before a bad model reaches a dashboard.

More in the field guide: Check row counts after every join and Joining more than two tables. Exercise J4 is this lesson’s problem.

Try it

List every account with its total deal amount and its number of contacts, A to Z. Accounts with no deals or no contacts should show 0, and the totals must not be inflated.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson