MySQL Multiplying float columns: 1*7.6 gives 7.599999904632568?

Question

When I make a query SELECT 1*7.6 , It gaves me 7.6

But when I make column multiplication (products.commision*sold.amount):

SELECT *,
(products.commision*sold.amount) as fee
FROM sold
RIGHT JOIN products
ON sold.idprod=products.ID
AND DATE(sold.date) BETWEEN DATE('2015-10-01') AND DATE('2015-10-31')
WHERE sold.userid="1"
ORDER BY sold.userid

It gaves me

ID | userid | date     | idprod | amount | Category | Name  | commision  | fee
60 | 1      | 15-10-01 | 21     | 1      | Volish   | Black | 7.6(float) | 7.599999904632568

Why?

You are using float columns. Decimal(19,6) is likely what you're looking for.... — Edward, Oct 14 '15 at 16:47
Probably because `products.commission` isn't exactly `1` or `sold.amount` isn't exactly `7.6`, but either value on it's own is close enough to be displayed as such. Floating point numbers are tricky. — Mr. Llama, Oct 14 '15 at 16:47
hmm I made these columns decimal but I can't set commision to 7.6? only 7 and 8? — , Oct 14 '15 at 16:55
[This answer by Skeet](http://stackoverflow.com/a/2545617/892420) is a good way to think about the difference. — Edward, Oct 14 '15 at 16:56

score 3 · Accepted Answer · edited May 23 '17 at 11:50

3

You would have to type-cast the values as integers/Decimals rather than as floats when making the comparison so

(CAST(products.commision AS Decimal(7,2)) * CAST(sold.amount AS Decimal(7,2))) as fee

from https://dev.mysql.com/doc/refman/5.0/en/cast-functions.html and casting a floating to decimal in mysql

The decimal sets the value as a decimal with the 7 digits the maximum size and the 2 digits the maximum precision (12345.67)

edit: I'm not sure if its more efficient to add the CAST to the raw values or to simply cast the answer such as

(products.commision  * sold.amount ) as CAST(fee AS Decimal(7,2))

edited May 23 '17 at 11:50

Community

1
1

answered Oct 14 '15 at 16:59

Martin

22,212
11
70
132

changing column types is probably best overall strategy, but you can change these things in the query if this is an isolated issue. – Martin Oct 14 '15 at 17:02
one more question, if i have 17,56 it is 17,56, but when i have 18,00 i want it just 18. how do i do it? – Oct 14 '15 at 17:03
where are you talking about? Where are these numbers, in the SQL query, or in the database, or? – Martin Oct 14 '15 at 17:04
If i have commision = `7.00` i want it to be `7` – Oct 14 '15 at 17:06
But when it's `7.54` i want it to be `7.54` – Oct 14 '15 at 17:06
1

for all intents and purposes `7.00` is exactly equal to `7`, if you're outputting the value to the browser then you can do some clearing up to remove excess zeros. http://stackoverflow.com/questions/5149129/how-to-strip-trailing-zeros-in-php – Martin Oct 14 '15 at 17:08
Oh i didnt know i can do it like that. Thanks! – Oct 14 '15 at 17:10

score 0 · Answer 2 · answered Oct 14 '15 at 16:59

0

Thanks for help in comments,

Solution

Float is binary, and decimal is... decimal, so i changed my columns to decimal(16,9)

answered Oct 14 '15 at 16:59

MySQL Multiplying float columns: 1*7.6 gives 7.599999904632568?

2 Answers2