SQL join double counting: why SUM inflates after a JOIN
SQL join double counting starts when a one-to-many join repeats rows before SUM runs: 3 timesheets and 2 expenses reported 44 hours for a true 22.
Three projects have budgets of 10,000, 4,000 and 2,500. Those add up to 16,500. Suppose a dashboard
reports 34,000 for the same three projects, and the only recent change is a new join that brought in
timesheet hours. The wrong number comes from where the SUM runs relative to the join, so the report
looks finished until someone adds the budgets by hand.
Here is a small schema you can paste into a scratch database:
CREATE TABLE projects (
id integer PRIMARY KEY,
name text NOT NULL,
budget numeric(10, 2) NOT NULL
);
CREATE TABLE timesheets (
id integer PRIMARY KEY,
project_id integer NOT NULL REFERENCES projects (id),
hours numeric(6, 2) NOT NULL
);
CREATE TABLE expenses (
id integer PRIMARY KEY,
project_id integer NOT NULL REFERENCES projects (id),
amount numeric(10, 2) NOT NULL
);
INSERT INTO projects VALUES
(1, 'Atlas', 10000.00),
(2, 'Borealis', 4000.00),
(3, 'Cirrus', 2500.00);
INSERT INTO timesheets VALUES
(1, 1, 8.00), (2, 1, 6.00), (3, 1, 8.00),
(4, 2, 4.00);
INSERT INTO expenses VALUES
(1, 1, 300.00), (2, 1, 300.00),
(3, 2, 150.00);Summing the budgets directly gives the right answer. Adding a join does not:
SELECT sum(budget) AS total_budget FROM projects;
-- 16500.00
SELECT sum(p.budget) AS total_budget
FROM projects AS p
JOIN timesheets AS t ON t.project_id = p.id;
-- 34000.00A join produces one output row per matching pair. Atlas has three timesheets, so its budget appears
on three rows and SUM adds it three times. Borealis has one timesheet and is counted once. Cirrus
has none and drops out of the inner join entirely. That is 3 x 10,000 + 4,000 = 34,000: too high for
one project and missing another, which is why the error can be hard to recognize by eye. Change it
to a LEFT JOIN and the total becomes 36,500, which brings Cirrus back and leaves the inflation in
place.
One join repeats the parent. Two joins to unrelated child tables also repeat the children, because nothing connects a timesheet to an expense. Each project gets every combination:
SELECT p.name,
p.budget,
sum(t.hours) AS hours,
sum(e.amount) AS spent
FROM projects AS p
LEFT JOIN timesheets AS t ON t.project_id = p.id
LEFT JOIN expenses AS e ON e.project_id = p.id
GROUP BY p.id
ORDER BY p.id; name | budget | hours | spent
----------+----------+-------+---------
Atlas | 10000.00 | 44.00 | 1800.00
Borealis | 4000.00 | 4.00 | 150.00
Cirrus | 2500.00 | |Atlas really has 22 hours and 600 of spending. Its 3 timesheets and 2 expenses became 6 rows, so each hour value was counted twice and each expense three times. The inflation factor differs for each column, and it differs by project.
One project, 3 timesheets, 2 expenses
Join first: 3 x 2 = 6 rows
8 h, 300 spent
8 h, 300 spent
6 h, 300 spent
6 h, 300 spent
8 h, 300 spent
8 h, 300 spent
SUM(hours) = 44, true value 22
SUM(amount) = 1,800, true value 600
Aggregate first: 1 row per project
timesheets by project: 22 h
expenses by project: 600 spent
joined: 22 h, 600 spent
Each side reaches the join at project grain, so no row repeats.
Look at the Borealis row. It has one child in each table, 1 x 1 is 1, and its numbers are correct. A test fixture built with one timesheet and one expense per project passes against this query.
Adding DISTINCT to each sum moves the Atlas numbers in the right direction, then past it:
SELECT p.name,
sum(DISTINCT t.hours) AS hours,
sum(DISTINCT e.amount) AS spent
FROM projects AS p
LEFT JOIN timesheets AS t ON t.project_id = p.id
LEFT JOIN expenses AS e ON e.project_id = p.id
GROUP BY p.id
ORDER BY p.id; name | hours | spent
----------+-------+--------
Atlas | 14.00 | 300.00
Borealis | 4.00 | 150.00
Cirrus | |Now Atlas is too low. The
PostgreSQL aggregate documentation
says the DISTINCT form runs the aggregate once for each distinct value of the expression. It
removes equal amounts, whether or not they came from the same source row. Two 8-hour days become one,
and two separate 300 expenses become one. COUNT(DISTINCT t.id) is a safe count, because id is
unique and the duplicates it removes really are the repeated rows. SUM(DISTINCT amount) is only
correct until two real rows share an amount.
Dimensional modeling has the word for what went wrong. Kimball's definition of grain is what a single row of a table represents, and it warns that different grains must not be mixed in one fact table. The same discipline applies to any query's intermediate result.
projects has one row per project, and budget is a fact about a project. timesheets has one row
per entry, and hours is a fact about an entry. Once you join them, the result has one row per
entry, and budget no longer matches the grain of the row it sits on. Summing it counts something
that does not exist at that grain.
Atlas's budget of 10,000 was summed at timesheet grain, so it counted three times. Aggregated at project grain, it counts once.
A grouped subquery brings a child table to project grain, with exactly one row per key:
WITH spend_by_project AS (
SELECT project_id, sum(amount) AS spent
FROM expenses
GROUP BY project_id
)
SELECT p.name, p.budget, coalesce(s.spent, 0) AS spent
FROM projects AS p
LEFT JOIN spend_by_project AS s ON s.project_id = p.id
ORDER BY p.id; name | budget | spent
----------+----------+--------
Atlas | 10000.00 | 600.00
Borealis | 4000.00 | 150.00
Cirrus | 2500.00 | 0The coalesce matters because a project with no children has no row in the grouped subquery. Timesheets
get the same treatment in a second grouped subquery with its own join, and Atlas then reports 22 hours
beside its 600, since neither child can repeat the other. A correlated subquery in the SELECT list, or
a LATERAL join, returns the same numbers. Which you pick is a matter of readability and of what the
plan does on your data. If you only need to filter by a child, EXISTS never multiplies anything:
budgets of projects with a timesheet sum to the correct 14,000.
EXISTS vs IN goes further into that family.
The fix is also usually faster, since the join has less to do. With 1,000 projects, each with 50
timesheets and 20 expenses, the fanned-out query built 1,000,000 intermediate rows and took 150 ms, in a
single EXPLAIN ANALYZE run on a local postgres:17 container. A single run of the pre-aggregated
version joined 1,000 rows to 1,000 rows in 9.6 ms. That million appears as actual rows on the join node,
and the database track guide covers reading plans like it.
An ORM does not change the arithmetic. In EF Core, this LINQ produces a plain inner join and the same 34,000:
var wrong = await db.Projects
.SelectMany(p => p.Timesheets, (p, t) => p.Budget)
.SumAsync();This one translates each navigation sum to its own correlated subquery. On EF Core 10 and the same data, it returned 22 and 600 for Atlas:
var report = await db.Projects
.OrderBy(p => p.Id)
.Select(p => new
{
p.Name,
p.Budget,
Hours = p.Timesheets.Sum(t => t.Hours),
Spent = p.Expenses.Sum(e => e.Amount)
})
.ToListAsync();The related problem on the loading side comes from the same
multiplication, when several collection Include calls fan out in one statement.
Seed at least two children per parent, with two of them equal. The Atlas project above has both, and
that one row exposes the fanout and the SUM(DISTINCT) shortcut at the same time.
Reconcile against the source. A report total should match the total of the table the measure came from, and this query makes the failure obvious:
WITH report AS (
SELECT p.id, sum(e.amount) AS spent
FROM projects AS p
LEFT JOIN timesheets AS t ON t.project_id = p.id
LEFT JOIN expenses AS e ON e.project_id = p.id
GROUP BY p.id
)
SELECT (SELECT sum(spent) FROM report) AS report_total,
(SELECT sum(amount) FROM expenses) AS source_total; report_total | source_total
--------------+--------------
1950.00 | 750.00Check the join key before you trust a join. GROUP BY project_id HAVING count(*) > 1 on the table you
are joining tells you whether it can repeat your rows. On timesheets it returns project 1 with 3
rows.
A one-child fixture cannot fail any of this, so the database exercises on Katabench that cover fanout, in raw SQL and in LINQ, check their totals on parents with several children.
Practice Databases & EF Core
- Open in the editor: Filter Before You Fetch
EF Core and SQL Easy Free, no account needed
Filter Before You Fetch
Filter measurements on the database, not after loading the whole table.
- Open the Pro kata: The Join That Doubled the Money
EF Core and SQL Hard Katabench Pro
The Join That Doubled the Money
Aggregate independent child facts to invoice grain before joining them, or every payment multiplies every credit.
More like this: EF Core practice →