Convert INT to FLOAT

Question

I have this query:

SELECT sl.sms_prefix, sum( sl.parts ) , cp.country_name, CAST(SUM(sl.parts) AS NUMERIC(10,4)) * CAST(cp.price AS NUMERIC(10,4))
FROM sms_log sl, sms_transaction st, country_prefix cp
WHERE st.customer_id =1
AND st.sendtime >=1329865200
AND st.sendtime <=1330037999
AND st.sms_trans_id = sl.trans_id
AND sl.sms_prefix = cp.prefix
AND st.customer_id = cp.customer_id
GROUP BY sl.sms_prefix
LIMIT 0 , 30

Result:

sms_prefix  sum( sl.parts )     country_name    price   total
==================================================================================
45            2                        Denmark   0.01   0.019999999552965
63            3                        Philippines   2  6

As you see the "total" is not correct for Denmark because sum( sl.parts )=2 Multiply with 0.01 the total should be 0.02.

Price field is FLOAT how I can CAST the total to float?

Answer 1

In oracle db there is a trick for casting int to float (I suppose, it should also work in mysql):

select myintfield + 0.0 as myfloatfield from mytable