2 floating nubers substract in a query

pelegk2

i have this query :

SELECT costumers.id as cID,sum(users_payment .total_bill-actuall_payed)
from costumers 
LEFT JOIN users_payment ON users_payment.userid=costumers.id  AND users_payment.month<5
group by cID
LIMIT 0, 50

in the first result i get instead of 92 i get : 91.199996948242
what can i do to prevent this? and get the correct answer?
by the way in the first line i am adding 2 number : 45.5 and 46.5=92
so whats the solution
tnaks in advance
peleg

nemonoman

So we better understand your problem, post the results of this query:

SELECT costumers.id as cID, users_payment .total_bill-actuall_payed
from costumers
LEFT JOIN users_payment ON users_payment.userid=costumers.id AND users_payment.month<5
group by cID

bradgrafelman

Are you positive the two numbers are exactly 45.5 and exactly 46.5?

mysql> SELECT (45.5 + 46.5)
-> 92.0

If you want a temporary fix, try rounding the numbers?

SELECT ROUND(45.5 + 46.5)
-> 92

or typecast them:

SELECT CAST(45.5 + 46.5 AS SIGNED)
-> 92

Drakla

Also remember that floating point representation is also not a precise thing. When you insert 1.0 or any other floating point number into a database float / double field, insert another one and then do a compare you might not even get true. I had to completely rewrite an accounting system because of this and change everything to pennies and divide by 100 on display.

But the manual does warn against using floating point maths for currency, or where true accuracy is needed. It sounds odd at first, but if you can put everything into an INT or DECIMAL type then do that instead.