One table:
items(id, price)
Has one row:
id: 1, price: 565.8
SELECT price FROM items
gives 565.8
SELECT SUM(price) FROM items
gives 565.799987792969
instead of 565.8
which I'd expect.
Where does the 565.799987792969
come from?
One table:
items(id, price)
Has one row:
id: 1, price: 565.8
SELECT price FROM items
gives 565.8
SELECT SUM(price) FROM items
gives 565.799987792969
instead of 565.8
which I'd expect.
Where does the 565.799987792969
come from?
I'm not sure what version you are using, but it sounds like this link describes what you are experiencing.
From the link:
mysql> select * from aaa;
+----+------------+------+
| id | month_year | cost |
+----+------------+------+
| 1 | 2002-05-01 | 0.01 |
| 2 | 2002-06-01 | 1.65 |
| 3 | 2002-07-01 | 0.01 |
| 4 | 2002-01-01 | 0.01 |
+----+------------+------+
mysql> select id, sum(cost) from aaa group by id;
+----+---------------------+
| id | sum(cost) |
+----+---------------------+
| 1 | 0.00999999977648258 |
| 2 | 1.64999997615814 |
| 3 | 0.00999999977648258 |
| 4 | 0.00999999977648258 |
+----+---------------------+
The SUM function changes 0.01 to 0.00999999977648258.
Floating point numbers are stored as approximate values; if you are storing a price, you are better off using a decimal datatype which stores precise values.