Check the receipts one by one and every amount is correct… yet when you total the month’s sales, the sum is one cent off from the bank deposit. Or you stored unit price × tax rate in a FLOAT column, and now the SUM refuses to balance. Most of these “each row is right, but the total is wrong” mysteries are not bugs in your code. They happen because floating-point numbers (FLOAT) can only hold most values approximately.
In this article we look at why the totals drift, using formulas and live, interactive graphs.
Fig. 1 through Fig. 3 are live graphs you can play with right on this page. Feel free to poke at the figures first and come back to the text afterwards.
If what you need is guidance on choosing column types, that lives in How to Choose the Right SQL Numeric Type (the classic “money in FLOAT” failure, and a FLOAT vs DECIMAL decision guide).
What this article answers
| Question | Where |
|---|---|
| Why does an everyday decimal like 0.1 cause trouble? | §1 / Fig. 1 |
| Why does an error invisible in one row show up in the total? | Fig. 2 |
| Do FLOAT and DECIMAL really diverge on the same receipts? | Fig. 3 / Table 1 |
| How does the famous 0.1 + 0.2 ≠ 0.3 fit in? | §5 |
| What should I actually do in practice? | §6 / FAQ |
To cover an enormous range of numbers within a fixed number of bytes, FLOAT / DOUBLE store most decimal fractions as an approximation rounded to the nearest representable value. That error is not a defect. It is a deliberate design trade: speed and range in exchange for exactness.
Which is exactly why these types are a poor fit for numbers that must agree down to the last cent, such as prices, tax rates, and invoice totals. There, DECIMAL (NUMERIC), which guarantees decimal digits, is the right answer. For quantities that carry measurement noise anyway, like sensor readings or statistics, FLOAT’s tiny rounding is usually a non-issue in practice.
A useful mental image is a ruler with missing tick marks. The perfectly ordinary decimal 0.1 does not land on any tick of the binary ruler; it gets pulled to the nearest tick, and repeated additions pile those pulls up. That is the anatomy of the one-cent gap in a monthly total.
1. Why decimal 0.1 gives binary a hard time
On paper, 0.1 is a clean, finite decimal. But inside the machine, FLOAT represents numbers in binary (zeros and ones), following the IEEE 754 standard. Convert 0.1 to binary, and it repeats forever.
\[ 0.1_{10} = 0.0001100110011\ldots_{2} \]
With only a finite number of bits, the value has to be cut off and rounded to the nearest tick. Writing that operation as \(\mathrm{fl}(x)\), what actually gets stored is not \(x\) but \(\mathrm{fl}(x)\).
\[ \mathrm{stored}(x) = \mathrm{fl}(x) \neq x \quad (\text{for many decimal fractions}) \]
A single rounding step is usually tiny. The trouble surfaces when you add the approximation over and over.
2. The tick marks FLOAT32 can actually hit (Fig. 1)
The figure below plots, as dots, the values that IEEE 754 single precision (FLOAT32) can actually represent around a target value of your choice. The dashed line is “the decimal value you want”; the filled dot fl is “the nearest tick FLOAT32 can hold”. Whenever the dashed line misses the dots, that miss is the rounding error.
Figure 1 The tick marks FLOAT32 can actually hit (number line and nearest fl)
The view opens narrow (8 ULP each side, i.e. eight tick spacings). Watch the gap between the dashed line (the value you want) and the blue dot fl (the nearest FLOAT32 value). Widen the slider and the ticks crowd together; narrow it and the individual spacing becomes visible.
Ideally, a total is just each amount added up as-is. In FLOAT, every addition rounds the result again.
\[ s_0 = 0, \qquad s_{k+1} = \mathrm{fl}(s_k + x_k) \]
Each individual \(\mathrm{fl}\) is small, but as \(k\) grows, the gap between \(s_n\) and the ideal total can become visible. This is how an error nobody notices on a single receipt surfaces for the first time in the monthly SUM.
# The idea (pseudocode) — x may differ from receipt to receipt
s = 0
for each receipt amount x_k:
s = fl(s + x_k) # rounded to the nearest tick every time
# The ideal is x_1+…+x_n. s may not match it.
3. Cumulative error as the receipts pile up (Fig. 2)
The horizontal axis is the number of additions \(n\); the vertical axis is the difference between the running FLOAT32 total and the ideal decimal total. Like receipts in a real shop, the amount \(x_k\) cycles through a pattern rather than staying constant (the slider sets the base amount).
Which way each rounding goes depends on where the running total sits between ticks (tick spacing, the ULP, changes with magnitude) and on the next amount \(x_k\). So the error is not condemned to creep monotonically upward: it can swing negative, come back, or jitter. The broad swells appear when the rounding bias points the same way for a while; the fine sawtooth comes from the rounding direction flipping as the amounts change.
One thing worth stressing: this wiggle is not randomness. Add the same amount to the same running total and you get the same result, every time. The receipt amounts in the figure follow a fixed multiplier cycle; no dice are rolled at run time. It may look erratic, but the mechanism is fully deterministic.
Figure 2 Cumulative error as you keep adding (FLOAT32 running total − ideal)
The point to take away: a difference invisible in one entry becomes visible as entries accumulate. Increase \(n_{\max}\) and compare the order of magnitude of the final gap in the panel readout.
4. Summing the same receipts in FLOAT and in DECIMAL (Fig. 3)
Fig. 2 plotted the size of the drift. Now we change the viewpoint and watch how the same column of receipts splits into two totals, one summed in FLOAT and one in DECIMAL, with the formula, a table, and a graph all in step. The amounts cycle through the same multiplier pattern as Fig. 2 (nominal base \(0.10\)).
DECIMAL adds each \(x_k\) exactly, in decimal. FLOAT32 inserts \(\mathrm{fl}\) at every step.
\[ S_{\mathrm{DECIMAL}}(n) = x_1 + x_2 + \cdots + x_n \]
\[ S_{\mathrm{FLOAT}}(n) = \mathrm{fl}\bigl(\cdots \mathrm{fl}(\mathrm{fl}(0 + \mathrm{fl}(x_1)) + \mathrm{fl}(x_2)) \cdots + \mathrm{fl}(x_n)\bigr) \]
Written as SQL, a minimal example looks like this. Same SUM, only the column types differ.
-- A minimal SQL example:
-- amounts vary per row, yet the same SUM can split by column type
SELECT SUM(amount_float) AS sum_float, -- FLOAT / REAL
SUM(amount_decimal) AS sum_decimal -- e.g. DECIMAL(10,2)
FROM sales;
In Table 1 below, the first few rows show almost no difference, and the gap only steps into the light on the final row, after the entries have piled up (the DECIMAL side is the exact total, computed at integer scale).
| Row | Receipt (as shown) | FLOAT32 running total | DECIMAL running total | Gap (F−D) |
|---|
Fig. 3 shows the same process as Table 1, but with the DECIMAL total as the baseline (zero gap). The vertical axis is not the total itself; it is FLOAT SUM minus DECIMAL SUM. The orange dashed line marks “zero gap (agrees with DECIMAL)”; the blue curve is how that gap evolves. The amounts change exactly as in Fig. 2, so the fine jitter is back (and again, it is not randomness).
Your intuition that “both totals climb up and to the right, with FLOAT drifting just a little” is correct. But the drift is so small relative to the totals that plotting both lines at full scale would collapse them into one. That is why the figure magnifies only the difference. When the blue curve dips or climbs, the total is not shrinking; the rounding direction changed along the way, pushing the FLOAT total below the DECIMAL total or pulling it closer.
Figure 3 FLOAT SUM − DECIMAL SUM (how the gap evolves)
Read them as a pair: Table 1 = the totals themselves, Fig. 3 = the difference, magnified. Making the money column DECIMAL is the choice that removes this blue drift before it starts.
5. The famous 0.1 + 0.2 ≠ 0.3 is the same story
The example endlessly discussed in language REPLs is the very same “missing tick marks”. We opened this article with drifting money totals; here the same core shows up in a different costume.
\[ \mathrm{fl}(0.1) + \mathrm{fl}(0.2) \;\neq\; \mathrm{fl}(0.3) \quad \text{(in most environments)} \]
In your browser’s JavaScript, the following is not strictly equal (display rounding can hide it).
0.1 + 0.2 === 0.3 // false 0.1 + 0.2 // 0.30000000000000004
Note that JavaScript numbers are double precision (FLOAT64). Its ticks are far finer than the FLOAT32 ticks in Fig. 1, but the picture is identical: 0.1 does not land on a tick. Switch Fig. 1 between 0.1, 0.2 and 0.3 and you can see each of them missing the ticks in the same view. The drifting monthly total and this REPL curiosity are the same missing tick, seen in two different places.
6. Putting it to work
- Prices, unit costs, tax rates, invoice totals — DECIMAL. If the data was stored in FLOAT, re-rounding later cannot always recover digits that are already gone.
- Sensor data, statistics, ML features — if measurement or model noise dominates anyway, FLOAT/DOUBLE is usually plenty.
- Display formatting (
toFixedand friends) and the type you store and aggregate in are different things. A tidy display changes nothing: if the DB column is FLOAT, the SUM can still drift.
Decision tables and CREATE TABLE examples live in How to Choose the Right SQL Numeric Type; this article stays focused on the mechanism of the drift.
Summary
- FLOAT holds most decimal fractions approximately; that is the price of handling a huge range fast.
- The root cause of the drift: clean decimal values like 0.1 do not land on binary tick marks.
- One rounding is tiny; a rounding on every addition becomes visible in the total.
- The same receipts can produce different SUMs in FLOAT and DECIMAL (Fig. 3).
- When the money must be exact, use DECIMAL. FLOAT’s error is by design; no amount of bug-hunting will remove it.
FAQ
Q1. How large is the error, concretely?
A. FLOAT32 carries roughly 7 significant decimal digits, DOUBLE (FLOAT64) roughly 15–16. “How many cents will it drift” depends on magnitudes, counts and the order of operations, so the practical answer is to drag the count in Fig. 2 and build intuition.
Q2. Is DOUBLE acceptable for money, then?
A. No. The extra digits merely make the drift harder to see; they are not a guarantee that decimal amounts are represented exactly. For money, DECIMAL is the answer.
Q3. Is this the same issue as 0.1+0.2 in programming languages?
A. Same family. SQL’s FLOAT and the default floating-point type of most languages share the binary approximation. It is a property of the representation, not some database-specific “weird bug”.
Q4. Same table, but the FLOAT SUM comes out slightly different between runs. Why?
A. Floating-point addition can change its result when the order of additions changes (associativity does not strictly hold). If parallel aggregation or a different index changes the order rows are read, the low digits of the SUM can wobble on identical data. This is the same “rounding depends on the running total” behaviour as in Fig. 2. A DECIMAL SUM agrees regardless of order.
Q5. We already stored money in FLOAT. Now what?
A. First, switch new writes to DECIMAL. Correcting historical data requires a business decision about which digits can still be trusted; mechanically re-rounding does not always restore the truth. See the “money in FLOAT” section of How to Choose the Right SQL Numeric Type.
Leave a Reply