SQL DISTINCT: when it fixes a count and when it hides a bug

DISTINCT removes repeated combinations of the selected expressions. It does not know whether two rows describe the same customer, the same event or two legitimate purchases with equal amounts. That business identity must come from your keys and data contract.

DISTINCT is useful for questions such as “Which customer IDs appear in completed orders?” It is dangerous as a general response to an unexpectedly large join result. It can conceal the relationship that caused the duplication and remove valid observations at the same time.

The synthetic commerce lab includes two kinds of repetition: legitimate repeated amounts and a deliberately replayed payment event. We will treat them differently.

Use DISTINCT to answer a set question

SELECT DISTINCT customer_id
FROM orders
WHERE status = 'completed'
ORDER BY customer_id;

The result contains six identifiers: C001, C002, C004, C005, C006 and C999. Repeated purchases by C001 and C002 do not repeat their IDs in the output.

That is appropriate because the question asks for a set of IDs. C999 remains an unmatched identifier, so the result is not automatically a list of six verified customer records.

Adding another selected column changes the definition of a duplicate. SELECT DISTINCT customer_id, order_id returns unique customer-order pairs; it no longer collapses multiple orders for the same customer. DISTINCT applies to the selected row combination. SQLite SELECT documentation.

Repeated amounts are not duplicate orders

The completed orders total 104,000 paise. Two are worth 12,000 each, and two are worth 10,000 each. Those equal amounts belong to different order IDs.

SELECT
    SUM(order_total_paise) AS correct_value_paise,
    SUM(DISTINCT order_total_paise) AS distinct_amount_value_paise
FROM orders
WHERE status = 'completed';

Expected output: 104,000 and 82,000. The distinct sum excludes one occurrence of each repeated amount, losing 22,000 paise of legitimate order value.

This is why SUM(DISTINCT amount) is not a safe repair for join fan-out. It operates on amounts, while the duplicated entity may be an order header repeated by item lines.

A replayed event needs a business key

The payment-event table contains P02 twice because the same event was ingested at two different times. Its business payload is unchanged. The raw sum is therefore 122,000 paise instead of the unique-event total of 104,000.

Inspect the repeated key first:

SELECT event_id, COUNT(*) AS received_rows
FROM payment_events
GROUP BY event_id
HAVING COUNT(*) > 1
ORDER BY event_id;

Expected: P02 with two rows. Now inspect its payload:

SELECT event_id, order_id, amount_paise, ingested_at
FROM payment_events
WHERE event_id = 'P02'
ORDER BY ingested_at;

The timestamps differ. Consequently, SELECT DISTINCT * would retain both rows. You need an explicit rule saying which fields identify a business event and which describe its delivery or ingestion.

For this exact fixture, the following collapses the identical business payload:

SELECT SUM(amount_paise) AS unique_event_value_paise
FROM (
    SELECT DISTINCT event_id, order_id, amount_paise
    FROM payment_events
);

Expected: 104,000. This is not a universal deduplication policy. If P02 arrived with a different amount, the query would keep both payloads. That conflict should trigger investigation rather than silently selecting whichever row is convenient.

Diagnose before discarding

When duplicates appear, ask:

• Is the business key actually unique in the source contract?
• Are these repeated deliveries, revisions or legitimate separate events?
• Did a join multiply previously unique rows?
• Do repeated keys have conflicting payloads?
• Can a corrected view be produced while retaining the raw evidence?

The answer determines whether to aggregate, select a defined version, quarantine a conflict or repair a join. Deduplication is a transformation with consequences, not a cosmetic cleaning step.

If you select the latest event version, define a deterministic ordering and a tie-breaker. Two records can share the same timestamp. If no authoritative tie-breaker exists, report the ambiguity instead of presenting an arbitrary winner as truth.

Keep before-and-after evidence

A useful deduplication report includes raw rows, unique business keys, repeated keys, conflicting payloads and the value difference introduced by the chosen policy. Preserve the raw table or immutable source files so the decision can be reviewed.

Exercise: change the second P02 amount in a copy of the fixture. Explain why a distinct payload query now returns two versions, and write the rule that should decide whether the later value is a correction or a data-quality failure. The correct answer depends on the source contract, which the modified data alone cannot establish.

NeuraPath's Data Analytics with Generative AI course covers SQL and wrangling where these decisions recur. A defensible analysis explains which duplicates were removed, why they were duplicates and how the retained result was checked.

Continue learning

This article is part of the SQL foundations for reliable analysis sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Calculate weighted average order value in SQL.
• Continue with UNION versus UNION ALL in monthly sales files.