The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
A JOIN can repeat a row from the table you are summing when that row matches multiple rows on the other side. SUM then adds the repeated value more than once. The SQL may be valid; the result is wrong for the question because the join changed the rows being aggregated.
How a JOIN can double a total
A join returns rows that satisfy its join condition. If one order matches two order-item rows, the joined result contains two rows for that order. PostgreSQL explains how join types combine rows in its table expressions documentation.
Suppose orders contains one row for order 101 with amount = 40, and order_items contains two rows for that order. After joining on order_id, the order amount appears twice. SUM(orders.amount) therefore returns 80 for that order.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Fix the driver behind crashes, sound loss and screen glitches3Clear out junk files and repair common Windows errorsSUM aggregates the values in its input rows; it does not know whether repeated values represent the same source record. PostgreSQL documents aggregate behavior in its aggregate functions reference, and Microsoft defines SUM similarly in its SQL Server documentation.
#1 Best Overall
Why GROUP BY does not fix fan-out
GROUP BY determines which input rows belong to each output group. It does not undo rows already multiplied by a join. If both repeated order rows fall into the same group, the aggregate still sees both copies. PostgreSQL’s aggregate tutorial shows aggregates operating over rows in each group.
Adding child columns to GROUP BY can produce more detailed groups, but it does not restore the original measure grain. The amount may simply appear in several output rows instead of one larger total.
Find the join that changes the row count
- State the measure grain. Write down what one value represents, such as “one amount per order” or “one revenue amount per invoice line.”
- Check the source table alone. Record its row count, distinct primary-key count, and total measure.
- Add joins one at a time. After each join, compare total rows and distinct keys from the measure-bearing table. A rising row count with an unchanged distinct-key count is a sign of fan-out.
- Locate repeated keys. Group by the measure table’s key and count the joined rows. Inspect keys with more than one match.
- Check the predicate and relationships. Look for missing key columns, incorrect date or status conditions, non-unique dimension keys, and unintended many-to-many relationships. Do not assume a column is unique because of its name.
- Reconcile the repair. Compare the result with the trusted source-table total and inspect representative keys, including those with no related rows and those with several.
Choose a fix that matches the intended calculation
Use EXISTS when related rows only determine eligibility
If the report should include an order when it has at least one billable item, but should still sum each order once, test for existence rather than returning every matching item row:
SELECT SUM(o.amount) AS total_amount
FROM orders AS o
WHERE EXISTS (
SELECT 1
FROM order_items AS i
WHERE i.order_id = o.order_id
AND i.is_billable = 1
);This keeps one outer row per order, provided orders.order_id is unique. Use this pattern only when the intended rule is “include orders with at least one qualifying item.” If the measure belongs at item grain, sum the item values instead.
Aggregate the child rows before joining when their values are needed
When the report needs item values alongside order values, first summarize items to one row per order:
WITH item_totals AS (
SELECT order_id, SUM(line_amount) AS item_total
FROM order_items
GROUP BY order_id
)
SELECT o.order_id, o.amount, i.item_total
FROM orders AS o
LEFT JOIN item_totals AS i
ON i.order_id = o.order_id;The grouped CTE has at most one row per order_id, so it will not expand an order row when the grouping key is correct. Decide whether the report should total o.amount, i.item_total, or both based on what each measure represents.
Summarize multiple many-side tables independently
If an order has several items and several payments, joining both raw child tables can create every item-payment combination for that order. Aggregate items to the order key and payments to the order key separately, then join those summaries. Otherwise, item totals can be repeated by the number of payments, and payment totals by the number of items.
Distinguish fan-out from other total differences
- Join direction and unmatched rows: An inner join removes unmatched parent rows; a left join retains them. Either join type can still return multiple matches and inflate a measure.
- Equal amounts are not duplicate records:
SUM(DISTINCT amount)removes repeated numeric values, not repeated source records. Two legitimate transactions with the same amount would be counted only once, so this is usually not a valid repair. - Empty input is different from repeated input: PostgreSQL documents that
SUMover no rows returnsNULL, not zero. UseCOALESCEif zero is the desired display value. This behavior does not explain an inflated total from fan-out. - Do not add DISTINCT indiscriminately: Removing duplicate output rows can hide legitimate records or change the query’s meaning.
The reliable fix is to align the query’s row grain with the measure: preserve one row per source measure key, or deliberately aggregate to a different grain before combining tables.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
