Maciej Maciej - 7 months ago 42
MySQL Question

Rounding in mysql doesn't work as expected

I can't understand why my results are different:

I have table orders and column price (type double in mysql).

The price value in database is 13.5.


SELECT ROUND(price * 0.09, 2) FROM orders where id = 1;

result is: 1.21


SELECT ROUND(13.5 * 0.09, 2);

the result is 1.22

without rounding:
SELECT 13.5 * 0.09
result is 1.215

so the correct result after round is 1.22.

Why query
SELECT ROUND(price * 0.09, 2) FROM orders where id = 1;
gives me wrong result (1.21)?

I can't understand what's wrong, I guess something with casting.

Could someone explain me it?

Answer Source

I the end I converted my columns to decimal 15,2. I was a bit afraid about the risk with converting, after few tests there is no difference in total sum of prices. I recommend to do it for everyone who has this problem.

Best data type to store money values in MySQL

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download