Incorrect decimals appearing in SUM MySQL

Question

I have the following SQL query.

SELECT SUM(final_insurance_total) as total
FROM `leads`
GROUP BY leads.status

I have a single row of data in the lead table with a value for final_insurance_total of 458796. The data type for final_insurance_total is float.

For some reason, MySQL is summing a single row as "458796.375".

If I change the query to

SELECT (final_insurance_total) as total
FROM `leads`
GROUP BY leads.status

the correct value is returned. What in the world is going on?

Answer 1

The FLOAT and DOUBLE types in MySQL (as well as in other databases and programming language runtimes) are represented in a special way, which leads to the values stored being approximations, not exact values. See MySQL docs, as well as general information on floating-point arithmetics.

In order to store and operate with exact values, use the type DECIMAL (see https://dev.mysql.com/doc/refman/5.1/en/precision-math-decimal-characteristics.html).

EDIT: I have run some tests, and while floating-point precision errors are quite common, this particular one looks to be specific to the implementation of SUM() in MySQL. In other words, it is a bug that has been there for a long time. In any case, you should use DECIMAL as your field type.