Mysql SUM function returns returns wrong decimal values

Question

I have the following table with the test data set:

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `desc` varchar(20) DEFAULT NULL,
  `amount` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

    insert into `test` (`id`, `desc`, `amount`) values('5',NULL,'847.3');
    insert into `test` (`id`, `desc`, `amount`) values('6',NULL,'-847');
    insert into `test` (`id`, `desc`, `amount`) values('7',NULL,'847.3');
    insert into `test` (`id`, `desc`, `amount`) values('8',NULL,'-847');
    insert into `test` (`id`, `desc`, `amount`) values('9',NULL,'847.4');

So the table looks like:

enter image description here

Now my problem is that when I use:

SELECT SUM(amount) FROM test;

I get the following results 847.9999999999999 instead of the expected 848.

Any ideas why I dont get the decimals rounded?

Update:

I am have tested this on MySQL Server: 5.5.17 (windows) and MySQL Server: 5.5.20 Centos

Use the `decimal` type rather than `double` which is floating point and so carries an inherent imprecision; http://stackoverflow.com/questions/6831217/double-vs-decimal-in-mysql — Alex K., May 15 '13 at 11:08
i would suggest you to use a DECIMAL type, instead of a DOUBLE — fthiella, May 15 '13 at 11:08
as you can see here http://sqlfiddle.com/#!2/cc2bf/1 your query and the result is fine. — AgeDeO, May 15 '13 at 11:48
i prefer a base 7 numbering system just to throw off the hackers — Drew, May 15 '13 at 12:23

RandomSeed · Accepted Answer · 2014-03-28T06:46:15.597

This is a problem inherent to the way floating-point numbers are represented by a computer. Basically, some values in base 10, which can be written with a finite number of digits, cannot be represented in base 2.

Most of the time, such approximations go unnoticed, because you only display a small number of decimal digits. But when you start adding and multiplying these approximate values, the error accumulates up to a point where it becomes noticeable.

This is why the DECIMAL type exists. It essentially represents a decimal value as an integer, divided or multiplied by a power of 10. With such a representation, no approximation is ever made.

Mysql SUM function returns returns wrong decimal values

1 Answers1