SUM() increases precision (decimal point) ?

[dlite922]

I have a query with number fields that contain floats with zero to three decimal places. When I SUM even just two of them together the sum() field fills up the precision up to the float max value.

example

1933.92 + 2335.78 summed -> 4269.70007324219

Calculator says: 4269.7 so Where did the 0.00007324219 come from?

Why does SUM() fill up the entire field. (ie if it was double, it would be even higher precision). I have to use ROUND() to put some sense back into it.

I'm no math genius but when you add two non-integer real numbers you get a result that has as many decimal points as the number that had the most decmial places.

e.g. 2.0013 + 5.2 = 7.2013


Any insight or explanation will be greatly appreciated!




Dan

[dlite922]

By the way, this is because my field is declared as a float.

If your field is a DECIMAL or FLOAT(12,2) this will not happen in MySQL 5 and up.

I've head this is because some numbers cannot be represented exactly in binary (computer base) vs base 10 (decimal).

I don't know exactly how and why not but Windows Calculator just says zero when I try to convert 0.5 to binary.

I thought base was just that, a base, any number can be represented in any base.




Dan

[Atli]

Floating point numbers are note considered 100% accurate.
I am not exactly sure of the reason for this (although, I suspect Google may help with that), but I have seen minor calculation errors like these when using them.

If you need to store your fractions with 100% accuracy (like to store monetary values), use DECIMAL.

@dlite922
As far as I know (and based on all that I know) this is not true.
All data can (and is) stored as binary on a computer. Computers run on binary, so any number that is used by a computer must therefore be convertible from and to binary.

Edit
After a little research... it turns out, some numbers (0.1 and 0.01) can not be represented *accurately* in binary. Their actual representation would be an infinite loop (similar to the decimal fractional value of 1/3), so they are actually represented as 4 counts of the loop and a 1 (in some cases).

As a result, when converting these numbers back from binary, these deliberate inaccuracies have to be taken into account, but it appears this is not always possible when using floats, which can sometimes lead to slight loss of accuracy.

Note that I didn't spend a lot of time researching this, so there may be more to it

[r035198x]

What every computer scientist should know about floating-point arithmetic

PS:What makes you sure your numbers are only zero to three decimal places?

[dlite922]

@r035198x
tl;dr; , i'll read it later.

Numbers are dollar amounts imported.





Dan