Why is the average receipt price wrong for purchased units?

Prepare the diagnostic input

The question is purchase cost per received unit for this batch set. AVG(unit_cents) gives each purchase row equal weight. Sum quantity times price, then divide by total quantity using decimal arithmetic and protect a zero denominator.

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 purchases(id TEXT PRIMARY KEY,sku TEXT,qty INTEGER,unit_cents INTEGER);
INSERT INTO purchases VALUES ('P1','A',1,1000),('P2','A',9,2000);

Reproduce the misleading result and correct it

SELECT sku,ROUND(AVG(unit_cents),2) AS average_unit_cents FROM purchases GROUP BY sku ORDER BY sku;

SELECT sku,ROUND(1.0*SUM(qty*unit_cents)/NULLIF(SUM(qty),0),2) AS average_unit_cents
FROM purchases GROUP BY sku ORDER BY sku;

One unit costs HK$10 and nine cost HK$20 each: 19000 cents over ten units gives 1900 cents per unit. The misleading average is 1500 cents. This weighted purchase measure is not a complete inventory costing method.

Check the boundary before applying the fix

Add a zero-quantity line priced at 9999 cents. It contributes no purchased units or cost, so the weighted result remains 1900 cents.

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 purchases VALUES ('P3','A',0,9999);

Freight, tax, returns and opening stock are excluded. Zero-quantity price quotations should usually be kept separate from accepted receipt data.

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.