CODEWITHSUNDEEP
mysql
Emanuil Rusev
Emanuil Rusev

Reputation: 35235

Using SUM on FLOAT data

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?

Upvotes: 27

Views: 64659

Answers (2)

Eugene Yarmash
Eugene Yarmash

Reputation: 149776

You can use the ROUND or FORMAT function:

SELECT ROUND(SUM(price), 2) FROM items;

Alternatively you can specify precision when defining a column, e.g. FLOAT(5,2).

Upvotes: 16

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32690

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.

Upvotes: 23

Related Questions