Oct 27, 2026 - 8 MIN READ

Why ROUND() Doesn't Fix Floating Point Errors in SQL

I rounded every number in my query and still got 0.9999999999999999. Here is why that happens, and four ways to fix it for good.

Daniel Naswan

I was building a report that adds up commission per branch. The numbers looked fine in my SQL client, but somewhere along the way a total turned into something like 0.00000000003 off from what it should be. My first fix was to wrap everything in ROUND(..., 2). It didn't work.

This post explains why rounding doesn't save you, and what actually does. You only need to know basic SQL and a bit of JavaScript. Every example below was run on MySQL 8.0 and Node.js 22 with Knex 3.3 and mysql2 3.23, and the outputs are copied straight from the terminal.

Part 1: Computers can't store 0.1

Most of us learned that 1/3 = 0.3333... goes on forever in base 10. Computers store numbers in base 2 (binary), and in binary, 0.1 goes on forever too:

0.1 (decimal) = 0.0001100110011001100110011... (binary, repeating)

A DOUBLE (and JavaScript's Number) uses the IEEE 754 standard: 64 bits, split into a sign bit, 11 bits of exponent, and 52 bits of fraction. It's basically scientific notation in binary. 52 bits is a lot, but it's not infinite, so the computer chops off the repeating part and keeps the closest number it can represent.

You can see what is really stored by asking for more digits:

(0.1).toPrecision(25)    // '0.1000000000000000055511151'
(1.005).toPrecision(20)  // '1.0049999999999998934'

So 0.1 is really "0.1 plus a tiny bit", and 1.005 is really "1.005 minus a tiny bit". These tiny errors are normally hidden because the computer prints the shortest number that maps back to the same bits. They show up once you start doing maths:

0.1 + 0.2   // 0.30000000000000004
1.1 * 3     // 3.3000000000000003
0.07 * 100  // 7.000000000000001

This isn't a bug in JavaScript or MySQL. Every language that uses IEEE 754 doubles does the same thing. (There's a whole website about it: 0.30000000000000004.com.)

SQL does it too

In MySQL and MariaDB, a plain number like 0.1 is an exact value, but a DOUBLE column or a number in scientific notation (0.1e0) is an approximate one:

SELECT 0.1 + 0.2, 0.1e0 + 0.2e0, 0.1e0 + 0.2e0 = 0.3e0;

+-----------+---------------------+-----------------------+
| 0.1 + 0.2 | 0.1e0 + 0.2e0       | 0.1e0 + 0.2e0 = 0.3e0 |
+-----------+---------------------+-----------------------+
|       0.3 | 0.30000000000000004 |                     0 |
+-----------+---------------------+-----------------------+

FLOAT is even worse, because it only has 32 bits:

-- price_f is a FLOAT column holding 1234.56
SELECT price_f, price_f * 1 FROM products;
-- 1234.56 | 1234.56005859375

The MySQL manual says it directly: floating-point numbers "are approximate and not stored as exact values."

Part 2: So why doesn't ROUND() fix it?

This is the part that confused me. Let's set up a small table. Ten orders, each with a price of 0.10, stored as DOUBLE:

CREATE TABLE orders (
  id    INT,
  price DOUBLE
);
-- insert 10 rows with price = 0.1

Adding them up gives the classic error:

SELECT SUM(price) FROM orders;
-- 0.9999999999999999

Fine, let's round each row first:

SELECT SUM(ROUND(price, 2)) FROM orders;
-- 0.9999999999999999

Still wrong! And it's not a display problem. Subtract 1 and you can see the leftover:

SELECT SUM(ROUND(price, 2)) - 1 FROM orders;
-- -1.1102230246251565e-16

That's -0.00000000000000011. So why didn't rounding help?

Reason 1: ROUND on a DOUBLE gives you back a DOUBLE

From the MySQL docs for ROUND():

The return value has the same type as the first argument (assuming that it is integer, double, or decimal).

So ROUND(0.1e0, 2) gives you a DOUBLE, and the closest double to 0.10 is... the same 0.1000000000000000055511151 we started with. Rounding can't produce an exact 0.10 because that number doesn't exist as a double. You can confirm the type by saving the result into a table:

CREATE TABLE t AS SELECT ROUND(price, 2) AS r FROM orders;
SHOW CREATE TABLE t;
-- `r` double DEFAULT NULL

It's still a double.

Reason 2: SUM adds the errors back up

When you write SUM(ROUND(x, 2)), you round each row, and then SUM adds ten doubles together, each a little bit off. The small errors pile up again, and you're back where you started. With more rows and bigger numbers, the leftover gets bigger. I loaded 2,000 random prices between 100 and 5000 into a DOUBLE column and a DECIMAL column side by side:

SELECT SUM(price), SUM(price_d), SUM(price) - SUM(price_d) FROM big;
-- 5014534.319999986 | 5014534.32 | -0.000000013969838619232178

That's exactly the kind of 0.0000000... leftover that started this whole investigation.

Reason 3: Your app gets the same broken number

Knex doesn't clean this up for you. Running it from Node gives the same result whether you use the query builder or knex.raw:

await knex('orders').sum({ total: 'price' })
// [ { total: 0.9999999999999999 } ]

const [rows] = await knex.raw('SELECT SUM(ROUND(price, 2)) AS total FROM orders')
// rows: [ { total: 0.9999999999999999 } ]

Side note: I first tried this on MariaDB 10.11, and there SUM(ROUND(price, 2)) displayed as 1.00, because MariaDB formats the result to 2 decimals when it sends it back as text. Subtracting 1 still gave -0.00, and a prepared statement (conn.execute()) still returned 0.9999999999999999. So even when the error looks hidden, it's still there.

Bonus: ROUND can round the "wrong" way

Because 1.005 is really 1.00499999..., rounding it can go down:

SELECT ROUND(1.005e0, 2), ROUND(1.005, 2);
-- 1               | 1.01

The exact value 1.005 rounds to 1.01 like you'd expect, but the double rounds down to 1. The MySQL docs also warn that for approximate values "the result depends on the C library", and on many systems it uses "round to nearest even" instead of the "round half up" you learned at school (source):

SELECT ROUND(2.5), ROUND(25E-1);
-- 3          | 2

Part 3: The error can also come from JavaScript

Even if your SQL is perfect, JavaScript can bring the error back. The mysql2 driver (which Knex uses under the hood) returns DECIMAL values as strings on purpose:

const rows = await knex('orders')
  .select(knex.raw('CAST(SUM(price) AS DECIMAL(15,2)) AS total'))

typeof rows[0].total  // 'string'
rows[0].total         // '1.00'

Its docs explain why: you can turn on decimalNumbers: true, but "this option could lose precision on the number as Javascript Number is a Float!"

So the moment you do Number(row.total) and start adding, subtracting, or multiplying in JavaScript, you're doing floating point maths again, and you can get things like 0.1 + 0.2 = 0.30000000000000004 or 4056.45 - 4056.42 = 0.02999999999974534.

Part 4: How to actually fix it

The rule is simple: don't let a floating point number touch money, anywhere in the pipeline.

Fix 1: Store money as DECIMAL

This is the real fix. DECIMAL(15,2) stores numbers in base 10, exactly, so 0.10 really is 0.10:

CREATE TABLE orders (
  id    INT,
  price DECIMAL(15,2),
  pct   DECIMAL(7,4)
);

SELECT SUM(price) FROM orders;  -- 1.00, exactly

The MySQL manual says that for DECIMAL columns, "MySQL performs operations with a precision of 65 decimal digits, which should solve most common inaccuracy problems." Summing the DECIMAL version of the column gave exactly '1.00', through both the MySQL client and Knex.

Fix 2: If you can't change the columns, cast before you do maths

Sometimes the table belongs to someone else. In that case, cast to DECIMAL on the way in, not just on the way out:

-- Bad: maths happens in DOUBLE, then you cast the damaged result
CAST(SUM(ROUND(price * pct / 100, 2)) AS DECIMAL(15,2))

-- Better: everything after the cast is exact decimal maths
SUM(ROUND(CAST(price AS DECIMAL(15,2)) * CAST(pct AS DECIMAL(7,4)) / 100, 2))

With the cast on the way in, SUM(ROUND(CAST(price AS DECIMAL(15,2)), 2)) returned exactly '1.00' through Knex, instead of 0.9999999999999999.

Casting the final result to DECIMAL(15,2) does hide most errors, because it rounds the double to 2 decimals when converting. But it rounds a value that is already slightly wrong, so a total that should be x.xx5 can land on the wrong side. Casting early avoids that.

Fix 3: In JavaScript, use a decimal library or integer cents

Keep the string the driver gives you, and do the maths with a library like decimal.js:

import Decimal from 'decimal.js'

new Decimal('0.1').plus('0.2').toString()          // '0.3'
new Decimal('1.005').toDecimalPlaces(2).toString() // '1.01'

Or work in cents as whole numbers, since integers up to 2⁵³ are exact in JavaScript:

const cents = ['19.99', '5.01'].map(s => Math.round(Number(s) * 100))
const total = cents.reduce((a, b) => a + b, 0)  // 2500
;(total / 100).toFixed(2)                       // '25.00', only for display

Only convert to a normal number at the very end, when you display it.

Fix 4: Never compare floats with `=`

If you're stuck with doubles, compare with a tolerance, like the MySQL manual recommends:

-- instead of WHERE a = b
WHERE ABS(a - b) <= 0.0001

Two more traps I found along the way

Round once, at the end, on purpose. SUM(ROUND(x, 2)) and ROUND(SUM(x), 2) can give different answers, even with exact DECIMALs. Three rows of 0.005 give 0.03 if you round each one first, and 0.02 if you add first (0.015 → 0.02). Neither is "wrong", but pick one on purpose. For things like commission, it's usually whatever the business rules (or your accountant) say.

UNION removes duplicate rows. If you combine several tables with UNION before summing, two rows that happen to be identical get merged into one, and your total quietly drops. Use UNION ALL when you're adding things up.

Summary

Problem	Why	Fix
`0.1 + 0.2 ≠ 0.3`	Binary can't store `0.1` exactly	Use `DECIMAL`
`ROUND()` doesn't help	`ROUND` on a `DOUBLE` returns a `DOUBLE`	Cast to `DECIMAL` before the maths
`SUM(ROUND(...))` is off	Summing doubles adds errors back	Sum `DECIMAL`s
Correct in SQL, wrong in app	`Number()` turns the `DECIMAL` string back into a float	Keep strings, use decimal.js or cents

Floating point is great for physics and graphics, where "very close" is good enough. Money needs to be exact, so give it an exact type from start to finish.