sqlmysql

Multiple Select from same table

Tablename: tbl_test

Code     |amount |Date   |Mos   |Remarks 
R1        300     1/7/25  Jul    Collect
R2        150     1/7/25  Jul    collect
R3        150     1/7/25  Jul    release    
R2        200     2/7/25  Jul    release
R3        200     3/7/25  Jul    release
R3        200     3/7/25  Jul    Cashout
R1        200     4/7/25  Jul    CashOut

I want to sum the amount and group by code

select
    code
    , sum(amount) as ttlcollect from tbl_test where remarks = 'collect'
    , (select sum(amount)) from tbl_test where remarks = 'release') as ttlrelease
    , (select sum(amount)) from tbl_test where remarks = 'cashout') as ttlcashout
group by code

How can I achieve the output below?

CODE    |ttlCollect     |ttlrelease     |ttlcashout
R1      |300            |0              |200
R2      |150            |200            |0
R3      |0              |450            |200
Solution

  • Something like this:

    SELECT code, 
    SUM(CASE WHEN Remarks = 'Collect' THEN amount ELSE 0 END) AS ttlCollect,
    SUM(CASE WHEN Remarks = 'Release' THEN amount ELSE 0 END) AS ttlRelease,
    SUM(CASE WHEN Remarks = 'Cashout' THEN amount ELSE 0 END) AS ttlCashout
FROM tbl_test
GROUP BY code