Is this the JOIN fan-out post?

Fan-out is the disease. DISTINCT is the painkiller. This post is what happens when the model 'fixes' a multiplied join by collapsing rows instead of changing the grain.

Is this UNION hiding duplicates?

Same DISTINCT idea, different clause. UNION (not UNION ALL) collapses overlap between two queries. SELECT DISTINCT collapses overlap inside one query. Both can delete real events.

When is DISTINCT correct?

When you actually want a set: unique emails, unique customer ids. It is not correct as a way to undo a one-to-many join before SUM(amount).

What about COUNT(DISTINCT order_id)?

That can recover an order count after a line-item join. It cannot recover SUM(amount) if amount was copied onto every line. Sum the orders table, or sum a subquery that is already one row per order.

Does DISTINCT ON have the same problem?

DISTINCT ON (customer_id) is a latest-row tool, and it needs ORDER BY. Without the order, Postgres still picks an arbitrary row per key — the same wrong-sibling bug as MAX(date) plus a random amount.

SELECT DISTINCT Join Band-Aid | Hidden Duplicates

You asked why last week's revenue was 3× the dashboard. The AI added SELECT DISTINCT (or COUNT(DISTINCT …) on the wrong columns). The number snapped close to what you expected. Nobody looked at whether two real charges on the same day survived.

DISTINCT does not fix a join. It hides it.

-- line items fan out orders; DISTINCT on these columns
-- collapses two $40 charges on the same order into one
SELECT DISTINCT o.id, o.amount
FROM orders o
JOIN line_items li ON li.order_id = o.id

If every order has the same id + amount on every joined row, DISTINCT undoes the fan-out and you get one row per order. That looks like a fix. It is also how you delete two legitimate orders that share an amount, or two charges that the warehouse stored as two rows with the same business key the model chose to DISTINCT on.

-- COUNT(DISTINCT) can save a count, not a sum
SELECT COUNT(DISTINCT o.id),   -- order count: often ok
       SUM(o.amount)           -- still 3× if amount is repeated per line
FROM orders o
JOIN line_items li ON li.order_id = o.id

The model reaches for DISTINCT because the fan-out post smell is "too many rows" and DISTINCT is the shortest English for "make rows unique." It never chose a grain. UNION is the same collapse between two queries.

DISTINCT ON (customer_id) without a matching ORDER BY is a third flavor: one row per customer, arbitrary sibling — the latest-row bug with extra syntax.

The actual fix is the grain

-- sum orders, not joined lines
SELECT SUM(amount) FROM orders
WHERE created_at >= DATE '2026-09-01'
  AND created_at <  DATE '2026-10-01'

-- or pre-aggregate lines, then join
SELECT o.id, o.amount
FROM orders o

If you needed line-level data, aggregate lines first (GROUP BY order_id) so the join is 1:1. Don't DISTINCT the wide row and hope.

How to catch it

COUNT(*) of the join
COUNT(DISTINCT order_id) of the join
SUM(amount) of the join
SUM(amount) FROM orders alone
If 1 and 2 differ, you have fan-out or real dups.
If 3 and 4 differ, DISTINCT will not save the money number.

Ask what column list DISTINCT used. If it is not a real unique key, unique-looking totals are a coincidence.

The prompt that heads this off

Do not add DISTINCT to 'fix' a high number. Show COUNT(*) vs
COUNT(DISTINCT id) vs the same aggregate on the left table alone.
If they disagree, change the join grain (subquery / GROUP BY),
do not collapse rows. DISTINCT only when I asked for unique values.

The short version

DISTINCT is a set operator, not a join repair. If the total looked right only after it appeared, you still don't know whether the join was wrong, the duplicates were real, or both.

Read-only MCP gateway: mcpserver.design.

Related reading: