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 ?
Regards,
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
While @Heximal's answer works, I don't personally recommend it.
This is because it uses implicit casting. Although you didn't type CAST
, either the SUM()
or the 0.0
need to be cast to be the same data-types, before the +
can happen. In this case the order of precedence is in your favour, and you get a float on both sides, and a float as a result of the +
. But SUM(aFloatField) + 0
does not yield an INT, because the 0
is being implicitly cast to a FLOAT.
I find that in most programming cases, it is much preferable to be explicit. Don't leave things to chance, confusion, or interpretation.
If you want to be explicit, I would use the following.
CAST(SUM(sl.parts) AS FLOAT) * cp.price
-- using MySQL CAST FLOAT requires 8.0
You can try the following to see what happens...
CAST(SUM(sl.parts) AS NUMERIC(10,4)) * CAST(cp.price AS NUMERIC(10,4))
Source: Stackoverflow.com