Quick answer: SUM(DISTINCT amount) sums unique amount values, not unique orders. If two legitimate orders both cost $100, it includes $100 once. Correct the join’s grain or aggregate its many-side before joining; do not use monetary values as order identifiers.

By Prakhar Shrivastava · October 8, 2026

A report can overcount and undercount the same orders

A fictional US/UK business has three orders worth $250 in total. US1 and US2 are separate $100 orders; UK1 is a $50 order. All amounts are already normalized to USD. The orders table has one row per globally unique order ID, while items has one row per item. Revenue is recorded at order level, not item level.

Joining every order to its items repeats US1 twice. A normal sum returns $350. Adding DISTINCT returns $150 because the two real $100 orders now collapse into one amount value. Neither total is correct.

Run the failing example

The CTEs below are a complete input dataset. These queries use common PostgreSQL-compatible syntax and were executed in SQLite for the result checks.

WITH orders(order_id, market, amount_usd) AS (
  VALUES ('US1','US',100), ('US2','US',100), ('UK1','UK',50)
), items(item_id, order_id) AS (
  VALUES ('I1','US1'), ('I2','US1'), ('I3','US2'), ('I4','UK1')
)
SELECT COUNT(*) AS joined_rows,
  COUNT(DISTINCT o.order_id) AS unique_orders,
  SUM(o.amount_usd) AS repeated_revenue,
  SUM(DISTINCT o.amount_usd) AS distinct_amounts
FROM orders o
JOIN items i ON i.order_id = o.order_id;

COUNT(DISTINCT order_id) answers a valid identity question in this fixture. It does not repair the separate sum expression. A dashboard can therefore show the correct order count alongside an incorrect revenue total.

Aggregate items before joining

For a market report containing order count, revenue and item count, reduce items to one row per order first. The final join then contributes each order’s amount once. COALESCE applies only to the missing item count here; it does not replace unknown revenue with zero.

WITH orders(order_id, market, amount_usd) AS (
  VALUES ('US1','US',100), ('US2','US',100), ('UK1','UK',50)
), items(item_id, order_id) AS (
  VALUES ('I1','US1'), ('I2','US1'), ('I3','US2'), ('I4','UK1')
), item_counts AS (
  SELECT order_id, COUNT(*) AS item_count
  FROM items
  GROUP BY order_id
)
SELECT o.market,
  COUNT(*) AS orders,
  SUM(o.amount_usd) AS revenue_usd,
  SUM(COALESCE(i.item_count, 0)) AS items
FROM orders o
LEFT JOIN item_counts i ON i.order_id = o.order_id
GROUP BY o.market
ORDER BY o.market;

market  orders  revenue_usd  items
UK           1           50      1
US           2          200      3

The two market totals reconcile to $250. Add an order (‘DE1′,’Germany’,75) without an item row: this LEFT JOIN retains it with $75 revenue and zero items, producing $325 overall. That is appropriate only if the report should include orders without items. Investigate whether those orders are valid, canceled or incompletely loaded before using the result.

If you only need to test for items, use EXISTS

A presence test need not expand orders into item rows. Using the same orders and items CTEs, replace the final SELECT with:

SELECT SUM(o.amount_usd) AS revenue_usd
FROM orders o
WHERE EXISTS (
  SELECT 1 FROM items i
  WHERE i.order_id = o.order_id
);

This returns $250 for the original fixture, regardless of how many item rows match each order. It excludes an order with no item match, unlike the previous LEFT JOIN. Choose the population deliberately.

Why not SELECT DISTINCT all the joined columns?

US1’s two joined rows have different item IDs, so both remain distinct if item_id is selected. Selecting only order_id and amount could remove the repeated copies in this small fixture, but it would also discard item detail. It is not a general repair for conflicting order records. If the same order ID has two different amounts, you need a source-of-truth or version rule rather than an arbitrary MAX, MIN or DISTINCT.

When source systems reuse order IDs, use a documented composite identity such as source_system plus order_id throughout the grouping and join. See the regional-sales identity example for a related failure when combining exports.

Checks before sending the report

• Verify one row per order key in the order source. The example assumes uniqueness; the CTE does not enforce a database constraint.
• Compare source order count and amount with the intended output population. Use the same filters, currency basis and reporting period.
• Include two different orders with the same amount in the test data. Otherwise a DISTINCT sum may appear to work accidentally.
• Test an order with several items and one without items. Confirm whether both belong in the report.
• Audit missing amounts separately: SUM ignores null inputs. A partial revenue sum is not a complete reconciled total.

For an interview explanation: “The join changed the grain from orders to order-item pairs. I will summarize items by order before joining, then reconcile revenue against the order source. DISTINCT on amount would remove legitimate equal-value orders.”

References and next practice

PostgreSQL aggregate expressions documents DISTINCT over expression values. The aggregate-function reference explains SUM and null inputs.

Continue with the SQL joins guide, orders and payments reconciliation exercise, or missing amounts versus zero.