Why does SUM(DISTINCT) lose legitimate stock receipts?

Prepare the diagnostic input

The receipt primary key already identifies separate accepted receipts. SUM(DISTINCT qty) discards equal numeric values even when the receipt IDs are different. If retry duplicates exist, resolve their record identity first rather than asking the aggregate to guess from quantity.

Use a fresh SQLite test database for the complete setup below. Every record is fictional; quantities are units and input monetary columns ending in cents use integer currency cents. Calculated averages may include fractions of a cent. Input previews include every table in this small fixture.

CREATE TABLE receipts(id TEXT PRIMARY KEY,sku TEXT,qty INTEGER);
INSERT INTO receipts VALUES ('R1','A',5),('R2','A',5),('R3','A',3);

Reproduce the misleading result and correct it

SELECT sku,SUM(DISTINCT qty) AS received_qty FROM receipts GROUP BY sku ORDER BY sku;

SELECT sku,SUM(qty) AS received_qty FROM receipts GROUP BY sku ORDER BY sku;

R1 and R2 each receive five units, and R3 receives three. The correct total is 13; the wrong query sums only the numeric values five and three, yielding eight. Equal receipt size is not evidence that an event was repeated.

Check the boundary before applying the fix

Add R4 with another five units and a new primary key. The total becomes 18, although the set of distinct quantity values is unchanged.

For the boundary test, recreate the complete setup in another fresh synthetic database, apply only the change below, then rerun the corrected query. Do not apply a test mutation to a live system.

INSERT INTO receipts VALUES ('R4','A',5);

Receipts are positive accepted events only. Returns, reversals, unit conversions and transport identifiers need explicit signed-event rules.

The displayed bad, corrected and boundary results were executed in SQLite and checked against separately authored expected rows. This demonstrates the stated fixture only; it does not test other database engines, production performance or your live schema.