Stop joins from multiplying SQL Server report totals

A report joins orders, lines, and payments, groups by order, and returns one row per order. The shape looks correct, yet the totals are wrong. GROUP BY can hide the extra joined rows without undoing their effect on SUM. The key question is the grain of each input: what real-world thing does one row represent?

Count combinations before summing them

An order with two lines and three payments produces six joined combinations when both child sets are joined independently by OrderId. Each line appears three times and each payment twice. The database is following the joins exactly; it is not accidentally duplicating stored rows.

The example deliberately gives both lines the same amount. That choice also exposes why SUM(DISTINCT Amount) is not a repair.

DECLARE @Orders TABLE (OrderId int PRIMARY KEY);
DECLARE @Lines TABLE
(LineId int PRIMARY KEY, OrderId int, Amount decimal(12,2));
DECLARE @Payments TABLE
(PaymentId int PRIMARY KEY, OrderId int, Amount decimal(12,2));

INSERT @Orders VALUES (1), (2);
INSERT @Lines VALUES (11, 1, 10), (12, 1, 10);
INSERT @Payments VALUES (21, 1, 5), (22, 1, 7), (23, 1, 8);

SELECT o.OrderId,
       SUM(l.Amount) AS WrongLineTotal,
       SUM(p.Amount) AS WrongPaymentTotal
FROM @Orders AS o
LEFT JOIN @Lines AS l ON l.OrderId = o.OrderId
LEFT JOIN @Payments AS p ON p.OrderId = o.OrderId
GROUP BY o.OrderId;

;WITH L AS
(
    SELECT OrderId, SUM(Amount) AS LineTotal
    FROM @Lines GROUP BY OrderId
),
P AS
(
    SELECT OrderId, SUM(Amount) AS PaymentTotal
    FROM @Payments GROUP BY OrderId
)
SELECT o.OrderId,
       COALESCE(l.LineTotal, 0) AS LineTotal,
       COALESCE(p.PaymentTotal, 0) AS PaymentTotal
FROM @Orders AS o
LEFT JOIN L AS l ON l.OrderId = o.OrderId
LEFT JOIN P AS p ON p.OrderId = o.OrderId
ORDER BY o.OrderId;

For order 1, the first query reports a line total of 60 and payment total of 40. The true totals are 20 and 20. The second query aggregates each child set to one row per order before joining, so those totals remain intact. Order 2 is retained with zero totals because the business interpretation here treats no lines or payments as zero.

SUM(DISTINCT l.Amount) would return 10, not 20, because it removes equal values rather than duplicate occurrences of a specific line. Two legitimate line items can have the same price. SELECT DISTINCT at the end cannot repair a sum that has already counted multiplied rows.

When investigating a larger query, temporarily remove the aggregation and select the parent key plus each child primary key. The combinations become visible. Count rows before and after every join and inspect the first point where the intended grain changes.

Aggregate at the grain the report actually needs

The CTEs in the corrected query each guarantee at most one row per OrderId through GROUP BY. Their names do not materialize them automatically; the logical grouping is what makes the join safe. Derived tables or suitable aggregate APPLY expressions can represent the same idea.

The correct grouping is not always just OrderId. A report by order and currency cannot safely collapse different currencies into one amount. A report by product needs line-level information that an order-only total has discarded. Write down the intended output key before selecting aggregation columns.

Apply filters to the appropriate measure. If the report asks for all order value but only settled payments, filter the payment input before its aggregation. Filtering the final joined rows can accidentally remove orders with no settled payment, especially when a right-side condition in WHERE turns a LEFT JOIN into an effective inner join.

When a child table is used only to require existence, use EXISTS rather than joining all its matching rows. For example, requiring at least one approved payment does not mean every approved payment should multiply the line set. Expressing that intention directly protects both meaning and often the amount of work.

Prove the result with asymmetric cases

Test an order with no children, one child on each side, several lines and one payment, and several children on both sides. Include equal amounts, partial payments, refunds, and any NULL amount state the schema permits. Symmetric one-to-one examples conceal the error.

COALESCE to zero is a reporting decision. If a missing aggregate represents unknown data or an incomplete import, converting it to zero can be misleading. Similarly, COUNT(*) after a LEFT JOIN counts the preserved parent row even when no child exists. Count a non-NULL child key when you mean actual children.

Uniqueness constraints on dimension keys protect another common source of fanout: a supposedly one-to-one lookup that contains duplicate mappings. Do not assume a name or an application convention proves uniqueness. Verify the key, including tenant and effective-date components where relevant.

Once correctness is established, inspect the actual plan and supporting indexes for the grouping and join keys. Pre-aggregation can reduce intermediate volume, but performance is secondary to a valid measure definition. Keep a small fixture with unequal child counts as a regression test so a later 'simplification' does not reintroduce inflated totals.

Technical references: Microsoft Learn: JOIN semantics · Microsoft Learn: SUM.