Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using SUM on FLOAT data

Tags:

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?

like image 605
Emanuil Rusev Avatar asked Oct 11 '10 14:10

Emanuil Rusev


People also ask

Can you use sum on a float?

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) . Thanks!

Can you use sum on floats in Python?

Python sum of floats Output: 7.0 If you want to add floating point values with extended precision, you can use math. fsum() function.

Can you sum a float in SQL?

float is a numeric data type and it can be used in SUM(). Here's what I used to test. Luis C.


2 Answers

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).

like image 21
Eugene Yarmash Avatar answered Oct 10 '22 20:10

Eugene Yarmash


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.

like image 179
LittleBobbyTables - Au Revoir Avatar answered Oct 10 '22 18:10

LittleBobbyTables - Au Revoir