CODEWITHSUNDEEP
mysql
user1029296
user1029296

Reputation: 609

Adding numbers with decimal returns rounded number in MySQL

I have a table with three column:

Source / Target / Weight

x / y / 0.2

x / y / 0.2

z / a / 0.5

The "weight" column is as "float." I am running a select to group all of the duplicates and add the "weight" scores together. Here is the query:

SELECT source, target, sum(weight) as weight2
FROM mytable
GROUP BY source, target

Oddly, after I run the query, it seems that any value below 1 in the "weight" section (e.g. 0.2) is rounded to 1. So I obtain the following table:

Source / Target / Weight

x / y / 2

z / a / 1

Where the scores should have been 0.4 and 0.5. What am I doing wrong?

Upvotes: 1

Views: 290

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562300

I just ran this on my instance of MySQL 5.5.30:

mysql> create table mytable (source char(1), target char(1), weight float);

mysql> insert into mytable values 
    -> ('x','y',0.2),
    -> ('x','y',0.2),
    -> ('z','a',0.5);

mysql> SELECT source, target, sum(weight) as weight2
    -> FROM mytable
    -> GROUP BY source, target;
+--------+--------+--------------------+
| source | target | weight2            |
+--------+--------+--------------------+
| x      | y      | 0.4000000059604645 |
| z      | a      |                0.5 |
+--------+--------+--------------------+

MySQL does not do rounding up to 1 as you describe. All I can guess is that you rounded up the values as you inserted them. I would recommend double-checking the data without doing a SUM() or GROUP BY, to see what the values are.

You may notice that in my output above, the SUM on the first row is not exactly 0.4, but instead it's a floating-point value near 0.4. You should probably not use FLOAT if you are concerned about rounding errors.

Read What Every Computer Scientist Should Know About Floating-Point Arithmetic, by David Goldberg.
Or a shorter treatment of this issue in the MySQL manual: Problems with Floating-Point Values.

Upvotes: 3

Related Questions