Aggregation After Joins: Solving the SQL Fan-Out Problem

1. What the fan-out problem is and why it happens

The fan-out problem describes one of the most common and at the same time hardest to detect sources of errors in aggregation after joins. It arises as soon as a row of the source table relates to several rows of a joined table through a JOIN, and this join is then aggregated directly without accounting for the multiplication beforehand. The result is sums that look plausible at first glance but are actually a multiple of the correct value.

The treacherous core of the fan-out problem is that the query itself does not throw an error. SQL executes the JOIN syntactically correctly, SUM and COUNT calculate correctly over the rows presented to them, only the number of these rows has been artificially inflated by the JOIN. From the database's perspective everything is fine, but from a business perspective the result is systematically wrong, often by exactly the factor a row was multiplied by.

This article shows with a concrete example how fan-out arises in aggregation after joins, why the underlying math is so treacherous, and which techniques systematically avoid the problem, from pre-aggregation in subqueries to window functions.

2. A concrete example: orders with items and payments

A classic scenario for the fan-out problem is a report on orders that should simultaneously show the sum of all order items and the number of payments per order. An order with three items and two payments becomes six rows through the join to both tables at once, the cartesian product of three items and two payments. A subsequent SUM over the item amount now counts this amount six times instead of three, because every item was combined with every payment.

The following example shows exactly this mistake in practice: the naive query returns an item sum that is exactly double the actual value, because two payment rows exist per order. At first glance this error often goes unnoticed, because the sum still seems plausible, especially when nobody recalculates the raw data row by row.

-- Fan-out in action: joining two one-to-many relations at once
-- order 100 has 3 line items and 2 payments -> 6 rows after both joins
SELECT
    o.order_id,
    SUM(oi.line_total) AS wrong_item_sum,   -- doubled: counted once per payment row
    COUNT(pay.payment_id) AS payment_count
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN payments pay ON pay.order_id = o.order_id
WHERE o.order_id = 100
GROUP BY o.order_id;

-- Result: wrong_item_sum is exactly double the real item total
-- order_id | wrong_item_sum | payment_count
--      100 |         240.00 |             2   (real item total is 120.00)

3. Why JOIN multiplies rows before aggregation happens

To master aggregation after joins correctly, one must understand the order in which a SQL query is actually processed. First, the database evaluates all FROM and JOIN clauses and produces a combined, potentially heavily enlarged intermediate result. Only afterward do WHERE, GROUP BY and the aggregate functions access this already multiplied set of rows. The aggregate function therefore never sees the original, unjoined rows, only the result of the cartesian product across all involved joins.

With a single JOIN over a one to many relation, the behavior is usually still intuitively understandable, because the multiplication corresponds to the number of related rows. It becomes critical as soon as two or more independent one to many relations are joined simultaneously, as in the example from section two. The multiplication factors of the individual joins then multiply with each other instead of adding up, which explains exactly the six rows in the example with three items and two payments.

4. Solution: pre-aggregation in subqueries before the join

The most robust solution to the fan-out problem in aggregation after joins is to pre-aggregate each one to many relation separately at order level first, before any join between the results takes place at all. Instead of joining the raw tables directly, a separate subquery or common table expression is written for each relation that is already aggregated at order level. Only these already aggregated intermediate results are then merged via the order key.

The decisive advantage of this technique: since each subquery aggregates independently of the others, no multiplication through another relation can occur anymore. The final join between the already aggregated intermediate results happens on a one to one basis via the order key, and a one to one join by definition does not multiply rows. This technique is the standard solution for aggregation after joins in production reporting code.

-- Correct fan-out-free aggregation: pre-aggregate each relation separately
WITH item_totals AS (
    SELECT order_id, SUM(line_total) AS item_sum
    FROM order_items
    GROUP BY order_id
),
payment_counts AS (
    SELECT order_id, COUNT(payment_id) AS payment_count
    FROM payments
    GROUP BY order_id
)
SELECT
    o.order_id,
    it.item_sum,
    pc.payment_count
FROM orders o
JOIN item_totals it ON it.order_id = o.order_id
JOIN payment_counts pc ON pc.order_id = o.order_id
WHERE o.order_id = 100;

-- Result: item_sum is now correct, no multiplication by payment rows
-- order_id | item_sum | payment_count
--      100 |   120.00 |             2

5. COUNT(DISTINCT ...) as a stopgap and its limits

A faster, but clearly more limited reaction to the fan-out problem is COUNT(DISTINCT ...) for counts. Instead of counting all multiplied rows, COUNT(DISTINCT o.order_id) counts only the unique orders, regardless of how often they were multiplied by the join. This technique works reliably for plain counting, but fails completely for SUM, because amounts cannot be deduplicated like identifiers: SUM(DISTINCT amount) would incorrectly merge together actually identical but legitimately different amounts as well.

This limitation makes COUNT(DISTINCT ...) a stopgap that only suits part of the fan-out problem. As soon as a query needs both counts and sums across multiple one to many relations, as in the example from section two, COUNT(DISTINCT ...) alone is not enough. For SUM, pre-aggregation in subqueries from section four remains the only consistently correct solution, regardless of the number of relations involved.

-- COUNT(DISTINCT ...) fixes counting, but NOT sums, in a fan-out join
SELECT
    o.order_id,
    COUNT(DISTINCT oi.item_id) AS correct_item_count,  -- works: counts distinct rows
    SUM(oi.line_total) AS still_wrong_sum               -- still multiplied by payments
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN payments pay ON pay.order_id = o.order_id
GROUP BY o.order_id;

6. Multiple one to many relations at once: the double fan-out

As soon as a query in aggregation after joins joins more than two independent one to many relations simultaneously, the row multiplication grows multiplicatively instead of additively. Three items, two payments and four comments on the same order produce twenty four rows after all three joins, the product of three times two times four. Every additional independent relation that gets naively joined intensifies the problem exponentially rather than linearly.

This exact multiplicative effect is what makes the fan-out problem so dangerous in more complex reports: errors that with two relations might still stand out as a moderate deviation quickly become an order of magnitude too high with three or four simultaneously joined relations, without any obvious error visible in the query. Pre-aggregation from section four, by contrast, scales linearly regardless of the number of relations, because each relation is aggregated separately and independently of the others.

-- Triple fan-out: 3 items x 2 payments x 4 comments = 24 rows per order
SELECT o.order_id, SUM(oi.line_total) AS wildly_wrong_sum
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN payments pay ON pay.order_id = o.order_id
JOIN order_comments c ON c.order_id = o.order_id
GROUP BY o.order_id;
-- item_sum would now be 8x too high (3 items x [2 payments x 4 comments])

-- Correct: pre-aggregate all three relations independently, then join once
WITH item_totals AS (
    SELECT order_id, SUM(line_total) AS item_sum FROM order_items GROUP BY order_id
),
payment_counts AS (
    SELECT order_id, COUNT(*) AS payment_count FROM payments GROUP BY order_id
),
comment_counts AS (
    SELECT order_id, COUNT(*) AS comment_count FROM order_comments GROUP BY order_id
)
SELECT o.order_id, it.item_sum, pc.payment_count, cc.comment_count
FROM orders o
JOIN item_totals it ON it.order_id = o.order_id
JOIN payment_counts pc ON pc.order_id = o.order_id
JOIN comment_counts cc ON cc.order_id = o.order_id;

7. Window functions as an alternative to pre-aggregation

Besides subqueries, window functions offer an elegant alternative for computing aggregation after joins without fan-out, especially when detail rows should be preserved alongside the aggregation. Instead of pre-aggregating and then joining, the sum is computed directly with SUM(...) OVER (PARTITION BY order_id) on the already joined, multiplied set of rows, but deliberately only on one of the involved detail tables, not on several at the same time.

This technique works reliably as long as only one of the involved one to many relations is summed with a window function, while further relations are handled separately via COUNT(DISTINCT ...) or their own window functions on their respective key column. With several relations that need to be aggregated at the same time, however, pre-aggregation in subqueries remains the clearer and more maintainable solution, because it structurally rules out the fan-out problem instead of working around it through clever window definitions.

8. Detecting fan-out bugs: checksums and test data

Because the fan-out problem does not trigger a SQL error, only systematic cross checking helps to detect it in existing reports. An effective technique is to compute the same metric once via the naive, joined query and once via an independent, guaranteed correct single query on just one table, then compare both values. If they diverge, this almost always points to fan-out in one of the involved instances of aggregation after joins.

For new reports, a deliberately crafted test dataset also pays off, in which at least one order intentionally has several items and several payments at the same time. Only with such test data does the fan-out problem become visible at all, because it does not occur with orders that each have exactly one item and exactly one payment, and the bug would remain undetected even though the query returns wrong values for real, more complex orders.

9. Approaches to aggregation after joins compared

The following overview ranks the presented techniques by reliability and area of use, to make the right choice from the start for future reports and avoid the fan-out problem occurring at all.

Anyone who builds reports from the start with pre-aggregated subqueries instead of naive multi joins never has to painfully untangle the fan-out problem from historically grown code later. This discipline pays off especially for metrics used in business decisions.

10. Summary

The fan-out problem arises because a JOIN multiplies rows before aggregate functions even begin calculating. With a single one to many relation, the multiplication is usually still obvious, but with several relations joined at the same time, the factors multiply, and sums become systematically too high without any recognizable error in the query. Aggregation after joins is therefore never trivial as soon as more than one one to many relation is involved.

The most reliable solution is to first aggregate each relation separately in its own subquery and then merge the already condensed intermediate results via one to one joins. COUNT(DISTINCT ...) only helps with plain counts, window functions suit individual relations where detail preservation is needed. Systematic cross checking with test data that deliberately contains several rows per relation reliably exposes fan-out bugs in existing code.

11. FAQ: Fan-Out Problem in Aggregation After Joins