SQL: Many-to-Many JOINs Inflate Totals
When both sides have repeated keys, a join creates every matching combination. Pre-aggregate to the grain you need.
The symptom
A revenue, cost, or activity total is wrong after a join, and usually far too high. The join key exists on every table involved, but two of them can hold more than one row for the same key, so the result is built from combinations rather than clean matches.
Why it happens
SQL joins rows, not concepts. Attach two detail tables to the same parent and the parent's row is duplicated once for every pair: a session with three pageviews and four events comes back as twelve combined rows, one per pageview-event combination. Nothing in the query is wrong on its own, but anything you count or sum after that join is counting a cartesian product.
The giveaway is that the two detail tables never join to each other. They are siblings, related only through the parent, so there is no pairing between them for the query to honour.
The fix
Aggregate each detail table to the grain you want before the final join, so every table entering that join already holds one row per key. Both queries below report how much activity each Website Analytics session generated across pageviews and events.
select s.session_id,
count(*) as rows_returned
from sessions as s
join pageviews as p on p.session_id = s.session_id
join events as e on e.session_id = s.session_id
group by s.session_id
order by s.session_idwith per_session_pageviews as (
select session_id, count(*) as pageviews
from pageviews
group by session_id
),
per_session_events as (
select session_id, count(*) as events
from events
group by session_id
)
select s.session_id,
coalesce(p.pageviews, 0) + coalesce(e.events, 0) as rows_returned
from sessions as s
join per_session_pageviews as p on p.session_id = s.session_id
join per_session_events as e on e.session_id = s.session_id
order by s.session_idSession 1 has 4 pageviews and 4 events — 8 rows of activity — and the joined query reports 16, because it multiplied them. Look further down and the damage is not even consistently upward: a session with one pageview and one event reports 1 where 2 rows exist, since multiplying is not inflating. "The number looks too big" is not a reliable smell test for this; counting the grain is.
Before joining two summaries, say the intended grain out loud: one row per session, customer, order, product, month, or channel. Then confirm each CTE really has that grain.