Why Your Total Is Wrong

I spent a decade as the person whose numbers went in front of decision-makers, and the failure I cleaned up most often was not bad data, stale filters, or anyone's arithmetic. It was a correct join doing exactly what joins do. The symptom is always the same: a report total that will not tie to the source it's supposed to match, and the gap is never a clean factor you could name in the meeting. It's off by 37 percent, this month, for reasons that move when you look at them.

This is join fan-out. Fan-out is the single most common way a competent person ships a wrong number.

Examples run on CellarDB (schema and setup here).

A column that was right last month

The blend program report starts life simple. Planned volume by vintage, straight off the Blends table:

SELECT Vintage, SUM(TargetVolumeGal) AS PlannedGal
FROM Blends
GROUP BY Vintage;

One row per blend in the table, every blend counted once, the total ties to the blend sheet. Correct.

Then someone asks a fair question: how much of that program is estate fruit versus purchased wine? The component detail lives in BlendComponents, and whether a component is estate is a fact about its wine lot. So the query grows two joins:

SELECT b.Vintage,
       SUM(b.TargetVolumeGal) AS PlannedGal,   -- same column as before
       SUM(CASE WHEN wl.FruitLotID IS NOT NULL
                THEN bc.VolumeUsedGal ELSE 0 END) AS EstateGal
FROM Blends b
JOIN BlendComponents bc ON bc.BlendID   = b.BlendID
JOIN WineLots wl        ON wl.WineLotID = bc.WineLotID
GROUP BY b.Vintage;

The new column is plausible. The old column, the one that was right last month, is now inflated roughly 4.7 times, and nothing about the query says so. The diff that broke it is a join everybody would have approved.

What the join did

A join's output takes the grain of the many side. Blends has about 60 rows; BlendComponents has about 280, because a finished blend draws on several wine lots. Join them and the result has ~280 rows. Every blend-level value now appears once per component:

SUM has no idea these are copies. It sees five twelve-hundreds and reports six thousand gallons of a twelve-hundred-gallon blend. Every aggregate over the one side is poisoned the same way: COUNT(*) counts components while you think it counts blends, and AVG(TargetVolumeGal) gets re-weighted by component count, so blends with more components count more.

The component-level column, SUM(bc.VolumeUsedGal), is correct in the same query. That's what makes fan-out vicious: the query is half right, and which half depends on which table each column came from.

The band-aid

The first thing most people try, once they see repeated values, is DISTINCT:

SELECT b.Vintage, SUM(DISTINCT b.TargetVolumeGal) AS PlannedGal
...

The number moves back toward plausible. It might even tie this month. It is still wrong, and now it's wrong in a quieter way: SUM(DISTINCT) deduplicates values, not blends. The moment two 2023 blends both target 1,200.0 gallons, they collapse into a single 1,200 in the sum. Two legitimately equal values is not an edge case; in any table with round planning numbers it's a certainty. SUM(DISTINCT) is not a fix with a caveat. It is a second bug stacked on top of the first, with the special property that it passes review because the total looks sane.

The one distinct-aggregate that has an honest job is COUNT(DISTINCT key), because a key is the entity: counting COUNT(DISTINCT b.BlendID) through the fan-out really does count blends. Everything else in the DISTINCT family is a way of treating the symptom.

That's the general principle: when you find yourself reaching for DISTINCT to clean up duplicates you don't understand, the duplicates are the message. Your result's grain no longer matches your question, and DISTINCT is how you stop the query from telling you.

The cure: aggregate first, then join

The structural fix is to collapse the many side to the join grain before it touches anything you intend to sum:

WITH ComponentSums AS (
    SELECT bc.BlendID,
           SUM(bc.VolumeUsedGal) AS ComponentGal,
           SUM(CASE WHEN wl.FruitLotID IS NOT NULL
                    THEN bc.VolumeUsedGal ELSE 0 END) AS EstateGal
    FROM BlendComponents bc
    JOIN WineLots wl ON wl.WineLotID = bc.WineLotID
    GROUP BY bc.BlendID
)
SELECT b.Vintage,
       SUM(b.TargetVolumeGal) AS PlannedGal,
       SUM(cs.EstateGal)      AS EstateGal
FROM Blends b
LEFT JOIN ComponentSums cs ON cs.BlendID = b.BlendID
GROUP BY b.Vintage;

The CTE produces exactly one row per blend, so the join is one-to-one, both sums are right in the same query, and the LEFT JOIN keeps blends whose components aren't entered yet instead of dropping them. This is my default and I apply it without waiting for symptoms: any table on the many side of a join gets pre-aggregated to the grain of the report before it meets a column I'm summing. Join aggregates to aggregates. Don't aggregate over joins.

The cost is a CTE and a few lines. The benefit is that the query's structure now matches the question's structure, which is the actual job.

Two fan-outs at once

The blends version is the trainer. The production version is worse, because fan-out compounds.

CellarDB's bulk side: BulkContracts holds original terms, ContractAmendments holds changes, Shipments holds wine leaving the building. Contract BC-2024-007 was signed for 12,000 gallons of Chardonnay at $8.50; a March amendment cut the price to $7.90, a June amendment raised the volume to 15,000, and three shipments of 5,000 gallons fulfilled it. A quarter-end recap wants booked value and shipped volume side by side, so the obvious query joins both:

SELECT c.ContractNumber,
       SUM(c.VolumeGal * c.PricePerGal) AS BookedValue,
       SUM(s.VolumeGal)                 AS ShippedGal
FROM BulkContracts c
LEFT JOIN ContractAmendments a ON a.ContractID = c.ContractID
LEFT JOIN Shipments s          ON s.ContractID = c.ContractID
WHERE c.ContractNumber = 'BC-2024-007'
GROUP BY c.ContractNumber;

Two independent one-to-many joins hang off the same parent, so they multiply each other: 2 amendments times 3 shipments is 6 rows for one contract. BookedValue comes back as 6 × $102,000 = $612,000, and the $102,000 it sextupled was already stale, because it's the original terms and the amendments were the point. ShippedGal comes back as 30,000, double the truth, because each shipment appears once per amendment.

Now zoom out to the full report across all 45 contracts. Only some contracts have amendments, and they have different counts of them, so each contract is inflated by its own private factor. The total is wrong by no ratio anyone can recognize, it changes whenever an amendment is filed, and it resists spot-checking because every unamended contract is exactly right. An error that's wrong everywhere by the same factor gets caught in review. An error that's wrong only where contracts were amended gets a meeting, and the meeting is about whether the data can be trusted, which is a worse meeting than it sounds.

The cure has not changed. Each many-side collapses to contract grain on its own, then joins one-to-one:

WITH Shipped AS (
    SELECT ContractID, SUM(VolumeGal) AS ShippedGal
    FROM Shipments
    GROUP BY ContractID
)
SELECT c.ContractNumber,
       c.VolumeGal * c.PricePerGal AS BookedValue,  -- original terms
       sh.ShippedGal
FROM BulkContracts c
LEFT JOIN Shipped sh ON sh.ContractID = c.ContractID;

Folding the amendments in correctly is a different problem from fan-out, because "current terms" means latest change wins, not sum of changes. The point here is narrower: amendments never belonged in the same join as shipments. Two facts about a contract at two different grains have to be brought to contract grain separately.

Catching it before it ships

Fan-out is easier to catch than to debug, and the checks are cheap.

Count rows before you aggregate. Run the FROM/JOIN skeleton with COUNT(*) and compare it to the count of the thing the report claims to be about. 45 contracts going in and 160 rows coming out is not an error; it's a fact about your joins, and either you wanted line-item detail or you just learned something important for free. The row count in the SSMS status bar is the cheapest instrument you own.

Tie one number to a source that can't be fooled. SUM(TargetVolumeGal) from Blends alone, no joins, is the control. If the report disagrees with the control, the report is wrong; there is no second possibility, because the control had nothing to fan out.

And before any of that, ask of the result what you should ask of every table: one row per what? If you can't answer for your own query's output, you don't know what your SUMs are summing. That question has a whole discipline behind it, and it's the next post.

— Lo