Skip to content
Katabench
Try free
8 min read The Katabench team

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.

The same table, two totals

Here is a small schema you can paste into a scratch database:

SQL
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:

SQL
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.00

A 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.

Two child tables multiply each other

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:

SQL
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;
Text
   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.

Values from the Atlas project in the example below. The two child tables have no relationship to each other, so the join pairs every timesheet with every expense.

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.

Why SUM(DISTINCT) is not the repair

Adding DISTINCT to each sum moves the Atlas numbers in the right direction, then past it:

SQL
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;
Text
   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.

Grain is the idea that fixes this

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.

Pre-aggregate, then join

A grouped subquery brings a child table to project grain, with exactly one row per key:

SQL
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;
Text
   name   |  budget  | spent
----------+----------+--------
 Atlas    | 10000.00 | 600.00
 Borealis |  4000.00 | 150.00
 Cirrus   |  2500.00 |      0

The 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:

C#
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:

C#
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.

A report test that fails before finance does

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:

SQL
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;
Text
 report_total | source_total
--------------+--------------
      1950.00 |       750.00

Check 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

More like this: EF Core practice →

Get new katas and .NET tips in your inbox

A short note when fresh kata land, plus the C# and performance tricks behind the grading. No spam, unsubscribe anytime.