开发者

mysql with 3 group by and sum

I have this data in my table (tb_cash_transaction)

mysql with 3 group by and sum

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜