CODEWITHSUNDEEP
mysqlsum
MaxiNet
MaxiNet

Reputation: 1028

MySQL SUM returns wrong value

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?

Upvotes: 0

Views: 2130

Answers (3)

J A
J A

Reputation: 1766

Have you tried

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

Upvotes: 0

DonCallisto
DonCallisto

Reputation: 29912

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

  1. precision of your machine (limited number of digit that you can store)
  2. representation error
  3. so on...

Upvotes: 0

juergen d
juergen d

Reputation: 204746

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

Upvotes: 2

Related Questions