CODEWITHSUNDEEP
mysqldivision
Warrior
Warrior

Reputation: 3304

Division in Mysql query

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...?

Upvotes: 5

Views: 23256

Answers (4)

Warrior
Warrior

Reputation: 3304

Both of the query type will work fine, problem was in the value, value stored in Database was of String type and also the value was coma separated (1,502.00) instead of (1502.00)

Upvotes: 0

jampez77
jampez77

Reputation: 5231

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);

Upvotes: 1

SlyChan
SlyChan

Reputation: 769

Looks like you have problem with first query only, because 1 / 6 = 0.166666666666667.

Upvotes: 1

jampez77
jampez77

Reputation: 5231

select sum(a.curVal) / sum(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;

It's not tested but you should have better luck with using SUM.

Hope this helps

Upvotes: 0

Related Questions