I have 2 different query which will return values : 1502.00 and 6
SELECT replace(CURRENT_VALUE,'$','') curVal
FROM form_attributes_values
WHERE TEST_ID=2
AND ATTRIBUTE_ID = (
SELECT ATTRIBUTE_ID
FROM form_attributes
WHERE FORM_ID=6
AND FORM_FIELD_NAME='REGRINDABLECUTTERCOST'
)
and
SELECT replace(CURRENT_VALUE,'$','') curVal
FROM form_attributes_values
WHERE TEST_ID=2
AND ATTRIBUTE_ID = (
SELECT ATTRIBUTE_ID
FROM form_attributes
WHERE FORM_ID=6
AND FORM_FIELD_NAME='REGRINDSPOSSIBLE'
)
I am trying to perform division query1/query2 (1502.00/6) which should return value 250.333333
but the value i am getting is something like 0.16666666666666
below are the types which is have tried so for:
Type 1:
select (a.curVal / b.curVal) as final_count
from (
SELECT replace(CURRENT_VALUE,'$','') curVal
FROM form_attributes_values
WHERE TEST_ID=2 AND ATTRIBUTE_ID = (
SELECT ATTRIBUTE_ID
FROM form_attributes
WHERE FORM_ID=6
AND FORM_FIELD_NAME='REGRINDABLECUTTERCOST'
)
) a,
(
SELECT replace(CURRENT_VALUE,'$','') curVal
FROM form_attributes_values
WHERE TEST_ID=2
AND ATTRIBUTE_ID = (
SELECT ATTRIBUTE_ID
FROM form_attributes
WHERE FORM_ID=6
AND FORM_FIELD_NAME='REGRINDSPOSSIBLE'
)
) b;
Type 2:
SELECT replace(CURRENT_VALUE,'$','') / (
SELECT replace(CURRENT_VALUE,'$','') curVal
FROM form_attributes_values
WHERE TEST_ID=2
AND ATTRIBUTE_ID = (
SELECT ATTRIBUTE_ID
FROM form_attributes
WHERE FORM_ID=6
AND FORM_FIELD_NAME='REGRINDSPOSSIBLE'
)
) curVal
FROM form_attributes_values
WHERE TEST_ID=2
AND ATTRIBUTE_ID = (
SELECT ATTRIBUTE_ID
FROM form_attributes
WHERE FORM_ID=6
AND FORM_FIELD_NAME='REGRINDABLECUTTERCOST'
)
both the type return 0.166666666666667 am i missing anything...?
解决方案
OK so i just tried this in the SQL console on PHPmyAdmin and got the answer 250.3333
SELECT cast(1502.00 AS unsigned) / cast(6 AS unsigned);