Free tools Windows power users keep installed
One-click scans. No signup required.
A JOIN can repeat a row that contains the value you are summing. The query may be valid SQL, but if one order matches several child rows, its amount appears several times in the joined result and contributes several times to the total. The fix is to match the query’s row grain to the measure: use a filter when related rows only determine eligibility, or aggregate the many-side before joining when its values are needed.
How a JOIN can inflate a total
A join combines rows that satisfy its join condition. When one row on one side matches several rows on the other, the result contains one joined row for each match. PostgreSQL describes joins as deriving a table from two inputs according to the join type and conditions: PostgreSQL: Table Expressions.
As an Amazon Associate I earn from qualifying purchases.
For example, suppose orders contains one row for order 101 with amount = 40, while order_items contains two rows for that order. A join on order_id produces two rows that each carry the order amount of 40. Summing orders.amount after that join returns 80 for order 101. The amount was not changed; the joined input now contains it twice.
Recommended Free Tools
SUM aggregates the values in its input rows, so repeated input values are included repeatedly. This is how PostgreSQL and SQL Server define the aggregate: PostgreSQL: Aggregate Functions and Microsoft: SUM (Transact-SQL).
#1 Best Overall
Why GROUP BY does not undo the multiplication
GROUP BY determines which input rows belong in each output group; it does not reverse row expansion caused by an earlier join. If two joined rows for an order fall into the same group, SUM sees both rows and adds both amounts. PostgreSQL’s aggregation tutorial demonstrates aggregates being computed over the rows in each group: PostgreSQL: Aggregate Functions Tutorial.
Adding child columns to GROUP BY can make the result more detailed, but it does not make a parent-level amount safe to sum. It may instead return several rows that each repeat the same parent amount. Choose the grouping grain based on what one report row is meant to represent.
Find which join changes the row grain
- State the intended grain. Write down what one measure represents, such as “one amount per order” or “one revenue amount per invoice line.” Identify the source key that uniquely identifies each measure-bearing row.
- Check the base table. Before joining, record its row count, distinct primary-key count, and total for the measure. This is the baseline for reconciliation.
- Add joins one at a time. After each join, compare the row count and the distinct count of the measure-table key. A rising row count with an unchanged distinct-key count signals that one or more measure rows now appear multiple times.
- Inspect repeated keys. Group the joined result by the measure-table key and count its rows. Examine keys with more than one match to see which related records create the expansion.
- Check the join condition and key uniqueness. Look for a missing key column, an incorrect date or status condition, a non-unique supposed dimension key, or an unintended many-to-many relationship. A column name that looks like an identifier does not prove uniqueness.
- Choose the intended relationship behavior. Decide whether related rows should only qualify a measure-bearing row, contribute their own values, or be summarized before the report is built.
- Reconcile the repair. Compare the repaired result with the trusted base-table total, and inspect representative keys: one with no related rows, one with a single match, and one with several matches.
Choose a fix that preserves the intended measure
Use EXISTS when child rows only determine eligibility
If the question is “What is the total for orders with at least one billable item?”, returning every qualifying item row is unnecessary. A semi-join-style EXISTS condition checks whether a match exists while keeping one outer row per order, assuming orders.order_id is unique:
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallCrashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteSELECT 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 counts an eligible order’s amount once, even if it has several billable items. It is appropriate only when the measure is at order grain and the item rows are a filter. If the desired measure is the sum of item values, aggregate those values instead.
Aggregate child values before joining
When the report needs both parent data and child totals, reduce the child table to at most one row per parent key before joining:
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 CTE groups by order_id, so it provides no more than one row for each order key. That prevents the item summary from expanding an order row, provided the grouping key and join key are correct. Which amount to total—o.amount, i.item_total, or both—depends on the report’s intended measures.
Aggregate multiple many-side tables independently
If an order has several items and several payments, joining both raw child tables can produce every item-payment combination for that order. For instance, each item row can pair with each payment row. Sum item amounts and payment amounts separately at order grain, then join the two summaries to the order table. Otherwise, item totals can be repeated by the number of payments, and payment totals by the number of items.
What other common fixes do—and do not—change
SUM(DISTINCT amount)is not a general repair. It removes repeated numeric values, not duplicate source records. Two legitimate orders with the same amount would be counted as one value in that aggregate.DISTINCTon the full result is not a reliable substitute. It can hide legitimate records or change the result’s meaning, depending on which columns are selected.- Changing INNER JOIN to LEFT JOIN does not prevent fan-out. A left join keeps unmatched parent rows, but a parent with several matches still appears several times.
- More GROUP BY columns can change the report grain. They may split a parent-level total across detailed rows rather than correct its repeated input.
Separate fan-out from NULL and zero behavior
A total can also be surprising when there are no input rows. PostgreSQL documents that SUM over no rows returns NULL, not zero; use COALESCE when zero is the intended display value: PostgreSQL: Aggregate Functions. That behavior is separate from fan-out: with fan-out, rows exist, but a measure-bearing row is repeated. Decide whether an empty result should display as NULL or zero independently of checking whether joins inflate existing values.
Keep the metric’s grain explicit
A many-to-one join from a measure table to a genuinely unique dimension usually preserves the measure table’s row count. A one-to-many join can expand it. The important checks are actual key uniqueness and the intended grain of each measure—not whether the SQL parser accepts the query. Across SQL implementations, syntax and optimizer behavior can vary, but a repeated input row still matters to a row-based aggregate.
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.
