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:

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

Answer 1

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.