CODEWITHSUNDEEP
mysql
cyberfly
cyberfly

Reputation: 5848

how to divide using mysql query

This is my query

SELECT currency_code, SUM(
CASE
WHEN TYPE = 'buy'
THEN to_amount
END ) AS BUY, SUM(
CASE
WHEN TYPE = 'sell'
THEN to_amount
END ) AS SELL, SUM(
CASE
WHEN TYPE = 'sell'
THEN rate
END ) AS SELL_RATE, SUM(
CASE
WHEN TYPE = 'buy'
THEN rate
END ) AS BUY_RATE
FROM tb_currency
LEFT JOIN tb_bill ON tb_currency.CURRENCY_ID = tb_bill.CURRENCY_ID
AND tb_bill.TYPE
IN (
'buy', 'sell'
)

The output is

enter image description here

Right now i want to divide the value in the BUY field with the value in the BUY_RATE field. I tried with several query before and got error message 'Unknown column type BUY'. How to solve this?

Upvotes: 1

Views: 5642

Answers (1)

Benjamin Seiller
Benjamin Seiller

Reputation: 2905

You need to do a subselect, something like this

SELECT *, (BUY/BUY_RATE) AS DIV_VALUE FROM (
    SELECT currency_code, SUM(
    CASE
    WHEN TYPE = 'buy'
    THEN to_amount
    END ) AS BUY, SUM(
    CASE
    WHEN TYPE = 'sell'
    THEN to_amount
    END ) AS SELL, SUM(
    CASE
    WHEN TYPE = 'sell'
    THEN rate
    END ) AS SELL_RATE, SUM(
    CASE
    WHEN TYPE = 'buy'
    THEN rate
    END ) AS BUY_RATE
    FROM tb_currency
    LEFT JOIN tb_bill ON tb_currency.CURRENCY_ID = tb_bill.CURRENCY_ID
    AND tb_bill.TYPE
    IN (
    'buy', 'sell'
    )
)

Upvotes: 1

Related Questions