mysql with 3 group by and sum
I have this data in my table (tb_cash_transaction)
I want to group the TYPE column, CURRENCY_ID column and AMOUNT column so it will become like below:
**Currency** **Cash IN** **Cash OUT** **Balance**
14 40000 30000 10000
1开发者_开发技巧5 50000 40000 10000
Rule :
1.Group by currency
2.Then find the sum of cash in for that currency 3.Find the sum of cash out for that currency 4.Get the balance (sum cash in - sum cash out)How to achieve it using mysql? I try using group by but cannot get the desired output.
Thanks in advance
SELECT currency_id,
SUM(CASE
WHEN TYPE = 'cash_in' THEN amount
END) AS cash_in,
SUM(CASE
WHEN TYPE = 'cash_out' THEN amount
END) AS cash_out,
SUM(CASE
WHEN TYPE = 'cash_in' THEN amount
ELSE -amount
END) AS balance
FROM tb_cash_transaction
WHERE TYPE IN ('cash_in', 'cash_out') /*<--- Where clause probably not needed
but just in case there are other options*/
GROUP BY currency_id
select currency_id, cashin "Cash In", cashout "Cash Out",
cashin-cashout "Balance" from (
select currency_id, sum(if(type='cash_in',amount,0)) as cashin,
sum(if(type='cash_out',amount,0)) as cashout,
from tb_cash_transaction group by currency_id
)
Try this:
SELECT a.currency_id, cash_in, cash_out, (cash_in - cash_out) balance
FROM (
SELECT currency_id, SUM(AMOUNT) cash_in
FROM tb_cash_transaction
WHERE type = 'cash_in'
GROUP BY currency_id
) a,
(
SELECT currency_id, SUM(AMOUNT) cash_out
FROM tb_cash_transaction
WHERE type = 'cash_out'
GROUP BY currency_id
) b
WHERE a.currency_id = b.currency_id
Here is the solution that i use. Thanks to cybernate for his code.
SELECT a.currency_id, cashin, cashout, (cashin - cashout) balance
FROM (
SELECT currency_id, SUM(AMOUNT) cashin
FROM tb_cash_transaction
WHERE type = 'cash_in'
GROUP BY currency_id
) a,
(
SELECT currency_id, SUM(AMOUNT) cashout
FROM tb_cash_transaction
WHERE type = 'cash_out'
GROUP BY currency_id
) b
WHERE a.currency_id = b.currency_id
精彩评论