MySQL SUM returns wrong value

Question

If I add a table like this in MySQL:

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` float NOT NULL,
  PRIMARY KEY (`id`)
);

and add an entry:

INSERT INTO `test` (`value`) VALUES ('123.45');

and do a SUM on it like this:

SELECT SUM( value )
FROM `test`

why does it return 123.449996948242 and not 123.45?

juergen d · Accepted Answer · 2012-05-24 15:38:39Z

2

This is because of floating point inaccuracy. Use DECIMAL data type instead.

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` decimal(8,2) NOT NULL,
  PRIMARY KEY (`id`)
);

See this example: SQLFiddle

answered May 24, 2012 at 15:38

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DonCallisto · Accepted Answer · 2012-05-24 15:38:55Z

0

Because a floating point number has some precision "problems" about:

answered May 24, 2012 at 15:38

DonCallisto

30k9 gold badges74 silver badges104 bronze badges

J A · Accepted Answer · 2012-05-24 15:47:52Z

0

Have you tried

SELECT 
      ROUND( SUM(value), 2)
FROM `test`

answered May 24, 2012 at 15:47

J A

1,7661 gold badge12 silver badges13 bronze badges