Join Fanout and Aggregate Correctness in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this Joining Multiple Tables, GROUP BY, NULL Handling in Joins and Aggregates
Builds toward Analyst Debugging Patterns
What is Join Fanout in SQL?
Join fanout is what happens when a join multiplies rows beyond what you intended, causing every aggregation that follows to compute over inflated data. The query runs without error, returns plausible-looking numbers, and gives no indication anything is wrong.
Here's the mechanism. You have an orders table and an order_items table, where each order has multiple line items. When you join them on order_id, you get one row per line item — not per order. An order with five items appears five times in the joined result. If you then sum orders.revenue (an order-level column), that revenue figure gets counted five times for the five-item order. The sum is five times too large, and nothing in the query or output flags the problem.
-- Fanout inflating revenue
SELECT
o.customer_id,
SUM(o.order_revenue) AS total_revenue -- wrong: counted once per line item
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.customer_idSELECT 'orders in the join' AS source, COUNT(DISTINCT o.id) AS row_count FROM orders o JOIN order_items oi ON oi.order_id = o.id UNION ALL SELECT 'rows after the join', COUNT(*) FROM orders o JOIN order_items oi ON oi.order_id = o.id
Customers with more line items appear to have disproportionately higher revenue. The tell is in those two counts: 91 orders go into the join and 100 rows come out, so some orders are being counted more than once. Sum an order-level column over those 100 rows and the total overstates real revenue — 58,561.81 becomes 71,814.77.
How do you check whether a join is fanning out?
Diagnosing fanout
Before aggregating, check the row count at the join level:
SELECT COUNT(*) AS joined_rows, COUNT(DISTINCT o.id) AS orders_in_join
FROM orders o
JOIN order_items oi ON oi.order_id = o.idIf joined_rows exceeds orders_in_join, the join is fanning out. Compare the two numbers from the same join rather than comparing against SELECT COUNT(*) FROM orders: an inner join drops unmatched rows as well as multiplying matched ones, so the joined count can land *below* the size of orders while still fanning out. That is exactly what happens here. Fanout is expected behaviour for a one-to-many join — the problem is aggregating an order-level column over the multiplied rows.
Make this check a habit: verify the join row count before writing the aggregation. It's faster to catch fanout here than to trace wrong numbers back through the query after the fact.
How do you fix join fanout in SQL?
The fix: pre-aggregate before joining
Collapse the right-side table to one row per key before the join runs:
WITH item_totals AS (
SELECT order_id, SUM(item_revenue) AS total_item_revenue
FROM order_items
GROUP BY order_id
)
SELECT
o.customer_id,
SUM(o.order_revenue) AS total_revenue
FROM orders o
JOIN item_totals it ON it.order_id = o.order_id
GROUP BY o.customer_idNow the join is one-to-one on order_id. No fanout. The aggregation is correct.
What happens to fanout across two one-to-many joins?
The one thing that trips people up
Fanout compounds across multiple joins. If you join orders to both order_items (5 items) and order_shipments (2 shipments), you get 10 rows per order — one per item-shipment combination. Aggregating any order-level column inflates it by a factor of 10.
The precondition for avoiding fanout is knowing the cardinality of every join before writing it: for each join key, can the right side have more than one row per key value? If yes, aggregating anything from the left side over that join is dangerous unless the right side is pre-aggregated first.
Practice Join Fanout in SQL
Scenario: Brightlane's operations analyst is sizing the row volume of a planned line-item revenue report before committing to its compute cost.
Task: Write a query to return the total number of (order, line-item) pairings on record — each line item paired with its parent order.
Assumptions:
- Every line item in
order_itemscorresponds to exactly one parent order, and an order may have multiple line items.
Output:
- One row, holding the total pairing count.
- Columns in this order:
joined_row_count.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution10 Join Fanout practice problems
Write a query to return the total number of (order, line-item) pairings on record — each line item paired with its parent order.
Write a query to return each order's id and the total revenue across its line items in order_items.
Write a query to return each order's id and the number of line items it contains.
Write a query to return each customer's customer_id and their total revenue across all line items in their orders.
Write a query to return each category name and the total revenue generated from line items across all products in that category.
Write a query to return each department name and the number of salary records on file for active employees in that department.
Write a query to return each order's id and the number of line items recorded against it.
Write a query to return the total number of pairings produced when each active employee is matched up with each of their salary records and each of their job-history records simultaneously.
Write a query to return each order's id and its item_revenue — the combined revenue across its line items, reported as 0 for orders with no recorded line items.
Write a query to return each customer's customer_id, the total number of line items purchased across all their orders, and the total revenue across those line items.
Start learning to practice all 10 Join Fanout problems, with instant grading and mastery tracking.
Common questions about Join Fanout
Does fanout affect COUNT as well as SUM?
It affects anything measured on the one side of a one-to-many join. A plain count of rows counts the multiplied rows; counting distinct ids on the original table gives the honest figure and is a quick way to see whether the multiplication happened.
Does adding DISTINCT fix a fanned-out SUM?
No, and it makes things worse in a new way. Summing distinct values removes genuine repeats too, so two orders that happen to cost the same are counted once. The fix is to aggregate the many side down to one row per key before joining.
How do you spot fanout before it reaches a total?
Count the rows after the join and compare that with the count of the table you are measuring. If the join made more rows than the table has, every figure computed from the joined result is measured over duplicated data.
