Why SUM(DISTINCT amount) Is Not a General Fix for JOIN Double Counting

A payment report can be wrong even when each table and foreign key is correct. The usual clue is a JOIN that multiplies rows before SUM runs.

This example uses one order, three payments, and two tags. The payments total 180, but joining the tags creates six rows: a plain SUM returns 360 and SUM(DISTINCT amount) returns 130.

The goal is to keep two valid 50 payments, remove only the JOIN-generated copies, and make the counted fact explicit. The examples compare identity-based deduplication with EXISTS, then separate those query problems from duplicate ingestion and allocation.

Build the Smallest Reproducible Case

Start with one order and three payment records:

Payment ID	Order ID	Amount
101	1	50.00
102	1	50.00
103	1	80.00

Payments 101 and 102 are separate transactions. Their amounts happen to match.

The correct payment total is:

50 + 50 + 80 = 180

The order also has two tags: MEMBER and COURSE. We want to total payments for orders that have at least one tag.

Run the SQL below in a fresh disposable database. It uses MySQL-compatible syntax and does not drop existing tables. Keep the setup and extra-order inserts separate so the fixture can be rerun cleanly.

-- Use a fresh disposable database. No existing tables are dropped.
CREATE TABLE sd_orders (
    id BIGINT PRIMARY KEY
);
CREATE TABLE sd_payments (
    id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES sd_orders(id)
);
CREATE TABLE sd_order_tags (
    id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    tag_code VARCHAR(30) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES sd_orders(id)
);
INSERT INTO sd_orders VALUES (1);
INSERT INTO sd_payments VALUES
    (101, 1, 50.00), (102, 1, 50.00), (103, 1, 80.00);
INSERT INTO sd_order_tags VALUES
    (201, 1, 'MEMBER'), (202, 1, 'COURSE');

The fixture treats every payment as valid and uses one currency. Production reports need explicit status, refund, tenant, and time policies, but those are not necessary to demonstrate this error.

See What the JOIN Actually Produces

Join the payment records to the order’s tags:

SELECT p.id AS payment_id, p.order_id, p.amount,
       t.id AS tag_id
FROM sd_payments AS p
JOIN sd_order_tags AS t ON t.order_id = p.order_id
ORDER BY p.id, t.id;

The result contains six rows:

Payment ID	Order ID	Amount	Tag ID
101	1	50.00	201
101	1	50.00	202
102	1	50.00	201
102	1	50.00	202
103	1	80.00	201
103	1	80.00	202

Each payment appears once for each tag. No payment record was duplicated in the source table; the query has produced multiple combinations involving it.

The distinction matters. We need to remove repeated representations of a payment from the monetary calculation, while retaining payments 101 and 102 as separate facts.

Now compare the two sums:

SELECT p.order_id,
       SUM(p.amount) AS joined_total,
       SUM(DISTINCT p.amount) AS distinct_value_total
FROM sd_payments AS p
JOIN sd_order_tags AS t ON t.order_id = p.order_id
GROUP BY p.order_id;

The output is:

Order ID	joined_total	distinct_value_total
1	360.00	130.00

The plain sum adds all six rows:

50 + 50 + 50 + 50 + 80 + 80 = 360

The distinct-value sum first reduces the amounts to two values:

Distinct amounts: {50, 80}
Sum: 50 + 80 = 130

Neither answer is the required 180.

What SUM(DISTINCT) Knows—and What It Does Not

MySQL defines SUM(DISTINCT expression) as a sum of the distinct values of that expression. It does not use a payment ID unless that identity is part of some separate operation in the query. MySQL aggregate-function documentation

When the expression is amount, the aggregate sees amounts. It does not know whether two occurrences of 50 represent:

• One payment repeated by a JOIN.
• Two independent payments.
• Duplicate ingestion of the same payment event.

Those distinctions require identity and business context.

SUM(DISTINCT) is not a broken function. It is correct when the requirement truly is to sum unique numeric values within each group. That is different from summing each valid payment once.

A dataset in which every payment amount happens to differ can hide the mistake. Tests need repeated legitimate values to expose it.

DISTINCT Applies to the Selected Row Shape

Switching to SELECT DISTINCT is not automatically enough. It removes duplicate result rows based on the selected expressions. Change the selected columns, and you change what counts as a duplicate. MySQL SELECT documentation

Selecting Only the Order and Amount Loses a Payment

Consider:

SELECT DISTINCT p.order_id, p.amount
FROM sd_payments AS p
JOIN sd_order_tags AS t ON t.order_id = p.order_id
ORDER BY p.order_id, p.amount;

The result is:

Order ID	Amount
1	50.00
1	80.00

Payments 101 and 102 have collapsed into the same selected tuple: order 1, amount 50.

Summing this result still gives 130.

Including the Payment Identity Preserves Both Payments

Now include the payment’s primary key:

SELECT DISTINCT p.id AS payment_id, p.order_id, p.amount
FROM sd_payments AS p
JOIN sd_order_tags AS t ON t.order_id = p.order_id
ORDER BY p.id;

The result is:

Payment ID	Order ID	Amount
101	1	50.00
102	1	50.00
103	1	80.00

The two JOIN-generated copies of payment 101 become one row, as do the copies of 102 and 103.

The two distinct 50 payments survive because their payment IDs differ.

This works under the fixture’s assumptions: the ID uniquely identifies a payment, and the selected order and amount come from that payment record.

Including the Tag Identity Retains All Six Rows

Add tag_id to the projection:

SELECT DISTINCT p.id AS payment_id, p.order_id, p.amount,
       t.id AS tag_id
FROM sd_payments AS p
JOIN sd_order_tags AS t ON t.order_id = p.order_id
ORDER BY p.id, t.id;

All six rows remain.

The rows for payment 101 differ in tag ID. They are not duplicate selected rows.

This is why “just add DISTINCT” is an incomplete instruction. A reviewer needs to ask: distinct according to which columns, and does that combination identify the fact being counted?

Repair the Query by Preserving Payment Identity

If an existing joined result must be reduced to its unique payment set, select that set before summing:

SELECT x.order_id, SUM(x.amount) AS paid_amount
FROM (
    SELECT DISTINCT p.id AS payment_id, p.order_id, p.amount
    FROM sd_payments AS p
    JOIN sd_order_tags AS t ON t.order_id = p.order_id
) AS x
GROUP BY x.order_id
ORDER BY x.order_id;

The result is:

Order ID	paid_amount
1	180.00

The inner query establishes one row per payment. The outer query aggregates those payments by order.

This is different from SUM(DISTINCT amount):

Approach	Unit retained once	Result
Sum joined amounts	Every joined row	360
Sum distinct amounts	Every different numeric value	130
Sum the distinct payment set	Every payment record	180

Do not add unrelated child columns to the inner projection without reconsidering its grain.

Also avoid treating this as a universal pattern for any calculated amount. If the amount legitimately varies by allocation or line item, payment ID alone may not describe the fact you need to retain.

Prefer EXISTS When Tags Only Determine Eligibility

For this specific requirement, there is an even clearer formulation.

We only need to know whether an order has any tag. We do not need tag records in the monetary result:

SELECT p.order_id, SUM(p.amount) AS paid_amount
FROM sd_payments AS p
WHERE EXISTS (
    SELECT 1
    FROM sd_order_tags AS t
    WHERE t.order_id = p.order_id
)
GROUP BY p.order_id
ORDER BY p.order_id;

This also returns 180.

EXISTS checks for a matching record without adding tag rows to the outer payment set. MySQL EXISTS documentation

The comparison is about expressing the requirement clearly. No performance advantage is claimed without measuring the relevant workload.

Both this query and the preceding identity-based query intentionally exclude payments for untagged orders. To make that behavior visible, add:

-- Run once after the initial examples.
-- This order has a valid payment but no tags.
INSERT INTO sd_orders VALUES (2);
INSERT INTO sd_payments VALUES (104, 2, 25.00);

Rerun either tagged-order query. Order 2 does not appear.

If the actual requirement is all orders, tags should not determine eligibility. For example:

SELECT o.id AS order_id,
       COALESCE(p.paid_amount, 0) AS paid_amount
FROM sd_orders AS o
LEFT JOIN (
    SELECT order_id, SUM(amount) AS paid_amount
    FROM sd_payments
    GROUP BY order_id
) AS p ON p.order_id = o.id
ORDER BY o.id;

The result includes order 1 with 180 and order 2 with 25. An order without payment records would remain with a displayed zero.

A rewrite that produces the desired total for one order may still change which orders are included. Verify membership as well as amounts.

Three Cases That Look Like Duplicates

The phrase “duplicate payment” can describe several situations.

Situation	Example	Appropriate response
Repeated representation from a JOIN	Payment 101 appears for two tags	Adjust query grain, use EXISTS, or recover the payment set
Duplicate ingestion of a business event	One channel transaction becomes two database rows	Establish business identity and enforce ingestion idempotency
Legitimate equal values	Payments 101 and 102 are both 50	Keep both

These are not interchangeable problems.

In the first case, the source table can be perfectly correct. The query must avoid counting the same fact repeatedly.

In the second case, distinct database IDs do not prove that the underlying business transactions differ.

In the third case, there is nothing to deduplicate.

Database Identity Is Not Always Business Identity

Imagine the payment table contains:

Row ID	Merchant	Channel	Transaction reference	Amount
501	7	CARD	txn-abc	50.00
502	7	CARD	txn-abc	50.00

Assume these rows are accidental repeated ingestion of one successful capture.

Selecting DISTINCT id, amount retains both rows and totals 100. It cannot recover the intended 50 because the physical record IDs differ.

The solution requires a business identifier whose uniqueness is defined by the payment provider and the application. It might involve merchant, channel, transaction reference, and event type.

Do not assume a transaction reference is globally unique. Do not collapse separate refunds, captures, or lifecycle events merely because they reference the same payment.

Once the identity contract is established, enforce it with an appropriate ingestion process and database constraint. Existing duplicates require investigation and controlled correction, especially if downstream balances or commissions already include them.

A reporting query that silently chooses one conflicting record does not repair the source data.

Some Relationships Need Allocation, Not Deduplication

Suppose a 100 payment covers two services.

If a report needs revenue by service, assigning 100 to each service counts 200. Keeping the payment under only one service loses the other service’s attribution.

Neither amount-based nor payment-ID-based deduplication defines the correct answer.

The system needs an allocation rule and corresponding detail, for example:

Payment ID	Service	Allocated amount
701	A	60.00
701	B	40.00

The allocation total remains 100, while each service receives its share.

The relevant grain is now a payment allocation, not merely a payment.

Deduplication removes repeated representations of a fact. Allocation determines how a fact’s value belongs to multiple reporting objects.

Before editing SQL, decide which problem the report actually has.

Choose the Query Shape from the Output Requirement

A useful decision table is:

Requirement	Starting approach
Sum payments for orders meeting a child-table condition	EXISTS
Show payment totals alongside other child-table totals	Aggregate each child to the shared reporting grain
Recover unique payments from an unavoidable joined result	Select payment identity and stable payment attributes before summing
Correct repeated ingestion of one business transaction	Business-level idempotency and source-data correction
Attribute a payment across several services	Explicit allocation records and rules
Sum unique numeric values	SUM(DISTINCT expression)

If a report combines several child tables, see Why Your SQL SUM Is Too High: Fixing Double Counting in One-to-Many JOINs. That article focuses on pre-aggregating each child table; this one focuses on identifying which fact should be counted.

There is no need to make the SQL more elaborate than the requirement demands. Avoid creating repeated rows merely to remove them later when an existence check can express the same condition.

Validate with Counterexamples

A test with three different payment amounts is insufficient. A faulty distinct-value sum could pass it.

The local verifier checks the following cases.

• Three payments totaling 180, including two equal amounts.
• Six rows created by the payment-to-tag join.
• A plain joined sum of 360.
• A distinct-value sum of 130.
• Two rows when projecting only order and amount.
• Three rows when projecting payment identity.
• Six rows again when including tag identity.
• Correct identity-based and EXISTS totals of 180.
• An untagged order excluded from both tagged-order queries.
• An all-order report that retains untagged and payment-free orders.
• Two physical IDs that still represent one duplicated business transaction.

The local verifier checks the fixture with SQLite: six joined rows, totals of 360 and 130, and identity-based and EXISTS results of 180. It also checks the untagged order and duplicate business-reference case.

The allocation example is a conceptual design illustration, not an implemented allocation engine.

To reproduce manually, run 01-setup.sql once in a fresh database, then 02-08 in order. Run 09-extra-order.sql once, rerun 07 and 08, and finish with 10-all-orders.sql. Use a fresh database for another complete run.

verify.py creates an isolated in-memory SQLite database; it does not connect to or alter an existing database.

A Short Review Checklist

Before changing the aggregate, write down what the report is supposed to count:

• a payment row;
• a provider transaction;
• an event;
• or an allocation line.

Payment 101 repeated for two tags is a query-shape problem. Rows 501 and 502 with the same provider reference may be an ingestion problem. Payments 101 and 102 with the same amount are two valid facts. The correct response depends on which case the data represents.

Define the identity first, then choose between EXISTS, identity-based deduplication, idempotency, or allocation.

Download the Complete Examples

The ZIP archive contains the SQL files used in this walkthrough and verify.py. The verifier uses an isolated in-memory SQLite database; the SQL setup is written for a fresh MySQL-compatible database. Download examples.zip if you want to reproduce the checks locally.