Why does a valid discount percentage produce zero?

Prepare the diagnostic input

The agreed example applies a whole-number percentage and rounds the final nonnegative discount to the nearest cent using SQLite ROUND. If discount_pct/100 runs first with integers, the fraction is truncated to zero. Changing display formatting cannot recover the discarded fraction.

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 offers(id TEXT PRIMARY KEY,price_cents INTEGER,discount_pct INTEGER);
INSERT INTO offers VALUES ('A',10000,15),('B',25000,0);

Reproduce the misleading result and correct it

SELECT id,price_cents*(discount_pct/100) AS discount_cents FROM offers ORDER BY id;

SELECT id,CAST(ROUND(price_cents*discount_pct/100.0,0) AS INTEGER) AS discount_cents FROM offers ORDER BY id;

A’s HK$100 offer has a 15% discount, or 1500 cents. The wrong query returns zero. B has zero discount and correctly stays zero in both queries, showing why a zero-discount test alone would miss the defect.

Check the boundary before applying the fix

A 99-cent item at 50% gives 49.5 cents before rounding and 50 cents under this fixture’s rule. Agree this boundary with the receiving system rather than assuming every rounding policy is the same.

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 offers VALUES ('C',99,50);

No tax, negative discount, compounded promotion or multi-line rounding is modelled. Other monetary policies require separately tested formulas.

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.