skip to content

Why can the SELECT-list expression price * quantity * 1.08 return unexpected decimals, and how should money arithmetic be written?

level: seniorimportance: nice to knowfreq 36%

answer

  1. Two unrelated causes, not one
  2. Ask what type the expression has
  3. Operand scales combine under multiplication
  4. Base two cannot hold one tenth
  5. Round once, where the value leaves

basics

~20 s

Multiplying exact numerics adds their scales, so a two-decimal price times a two-decimal rate yields four decimals; if the columns are binary floating point instead, the value is inexact from the start. Use DECIMAL columns and round once, deliberately.

solid answer

~50 s

Two separate effects are at play. With **exact numeric** types, the standard derives the result's scale from the operands: multiplication adds them, so `DECIMAL(10,2) * DECIMAL(3,2)` has scale 4 and you get `10.8000`, not `10.80`. That is not an error — it is the expression's declared type — but it surprises anyone expecting cents. With **binary floating point** (`REAL`, `DOUBLE PRECISION`, `FLOAT`), values such as 0.1 cannot be represented exactly, so sums and products drift by tiny amounts that surface as one-cent discrepancies. The rule is: store money in `DECIMAL`/`NUMERIC`, never in a float; let the intermediate expression keep its full scale; and apply exactly one rounding at the point where the value becomes an output — `ROUND(price * quantity * (1 + tax_rate), 2)`. Rounding each factor first, or rounding twice at different levels, produces totals that no longer reconcile.

code

sql · 9 lines
sql
-- price DECIMAL(10,2), tax_rate DECIMAL(4,3), quantity INTEGER

-- Correct but surprising: the product's scale is the sum of the operand scales
SELECT price * quantity * (1 + tax_rate) AS raw_line_total
FROM order_lines;

-- Full-scale arithmetic, one deliberate rounding at the output boundary
SELECT ROUND(price * quantity * (1 + tax_rate), 2) AS line_total
FROM order_lines;

go deeper

for a junior

Know that money goes in DECIMAL or NUMERIC rather than a floating-point type, and that a computed amount often needs an explicit ROUND before it is displayed.

for a middle

Explain that operand scales determine the expression's scale — multiplication adds them — and separately that binary floating point cannot represent decimal fractions exactly, so the two symptoms have different fixes.

for a senior

Show that you treat rounding as a boundary decision rather than a formatting step: one rounding, in one agreed place, so line totals and invoice totals reconcile, with the engine's tie-breaking rule verified rather than assumed.

for a principal

Own the money-arithmetic contract across the system: which stage rounds, what scale each stage carries, how currencies with different minor units are handled, and where that rule is expressed once so services cannot diverge.

## Two different causes, often confused When a monetary column comes back "wrong", it is almost always one of two unrelated things. **Cause one: the expression's declared scale.** SQL derives an arithmetic expression's type from its operands. For exact numerics the standard fixes the scale of a product as the sum of the operand scales: `DECIMAL(10,2) * DECIMAL(3,2)` has scale 4. So `9.99 * 1.08` comes back as `10.7892`, and `price * quantity` where quantity is `INTEGER` (scale 0) keeps scale 2. Nothing is broken — the value is exact, it simply carries more digits than a currency amount. Division is different again: the standard leaves the result's precision and scale implementation-defined for division, which is why the same ratio can come back with different digit counts on different engines. **Cause two: binary floating point.** `REAL`, `DOUBLE PRECISION` and `FLOAT` store values in base 2, and decimal fractions like 0.1 and 0.2 have no exact base-2 representation. Arithmetic on them is approximate, so a sum of many small amounts can land a fraction of a cent away from the exact figure, and two paths to the same total can disagree. `SELECT 0.1 + 0.2` evaluated in double precision is the canonical demonstration. The first cause produces a *correct but oddly formatted* number. The second produces a *slightly wrong* number. Diagnose which one you have before you "fix" it, because the remedies are different: formatting versus a type change. ## Choose the type first Money belongs in an exact numeric type — `DECIMAL(p, s)` or its synonym `NUMERIC(p, s)` — with a scale chosen for the currency. Exact numerics store the value in decimal terms, so 0.10 is exactly 0.10 and adding it ten times gives exactly 1.00. Floating point is for measurements and scientific quantities where relative precision matters more than exact decimal representation; it is never the right choice for amounts that must reconcile to the penny. A subtle consequence: a numeric literal like `1.08` written in a query is an exact numeric with scale 2, whereas `1.08E0` is approximate. Mixing an exact-numeric column with a floating-point operand promotes the whole expression to floating point and reintroduces the inexactness you were trying to avoid. ## Round once, at the boundary The design rule that keeps totals reconcilable is: let intermediate arithmetic keep its full scale, and round exactly once, where the value leaves the computation: ```sql SELECT order_line_id, ROUND(price * quantity * (1 + tax_rate), 2) AS line_total FROM order_lines; ``` Compare the two wrong shapes. Rounding the inputs first — ```sql ROUND(price, 2) * ROUND(quantity * (1 + tax_rate), 2) ``` — throws away information before the multiplication and compounds the error. Rounding twice at different aggregation levels is worse: if each line total is rounded to cents and then those rounded values are added, the result can differ by several cents from rounding the un-rounded total once. Neither is "more correct" in the abstract — but a system must pick one convention and apply it everywhere, or the invoice will not equal the sum of its lines. In most accounting contexts the rule is that the line total is the rounded, stored artefact and every higher total is the sum of those stored line totals, so the rounding boundary is a business decision that the SQL then implements consistently. ## Know your ROUND `ROUND` is available in all the major engines but is not part of the standard's core, and its details vary. The half-way rule can be round-half-away-from-zero or round-half-to-even depending on engine and on the operand's type — PostgreSQL, for example, rounds `numeric` and `double precision` by different rules. Some engines only accept a digit-count argument for exact numeric types. If a rounding convention is financially material, verify it on your engine rather than assuming, and consider expressing it once in a view so every consumer inherits the same rule. ## What to say in an interview Name the two causes separately, state that money is `DECIMAL` and never float, explain that the operand scales determine the expression's scale, and then make the design point: rounding is a boundary decision, applied once, in one agreed place, so that line totals, invoice totals and ledger totals reconcile. That combination — type discipline plus a single rounding boundary — is what distinguishes an answer from a formatting tip.

  • Why does summing rounded line totals sometimes differ from rounding the summed total?
    Because each rounding discards up to half a unit in the last place, and those residuals accumulate across rows instead of cancelling. Neither result is inherently correct; the system has to choose a convention. Most accounting rules make the rounded line total the authoritative artefact and define every higher total as the sum of those stored values, so the SQL must follow that boundary consistently.
  • Is DECIMAL(10,2) always the right money type?
    The scale must match the currency and the stage of computation. Two decimals suit most consumer currencies, but some currencies have zero or three minor digits, and unit prices or FX-converted intermediates often need more scale than the final amount. Choose the precision to cover the largest expected magnitude and the scale to cover the smallest meaningful unit at that stage.
  • What happens if one operand in a money expression is a floating-point value?
    The whole expression is promoted to floating point, so an exact `DECIMAL` column loses its exactness for that computation and the result can drift by fractions of a cent. That includes literals written in exponent form and columns someone declared as `FLOAT` years ago. Keep every operand in the exact-numeric domain, casting explicitly if a source column is approximate.

saying these in an interview costs you the question

  • Stores currency amounts in FLOAT or DOUBLE PRECISION
  • Blames the engine for a decimal that is actually exact
  • Rounds every factor before multiplying them
  • Assumes ROUND breaks ties the same way everywhere
  • Rounds at several levels and expects totals to reconcile

context

resolving links…