split one fields into multi columns based value in other field

Question

My data is stored in mysql.

My Table data is as follows

3   credit  500.00
3   debit   500.00
4   credit  300.00
4   debit   300.00
5   credit  300.00
5   debit   300.00
6   credit  300.00
6   debit   300.00

I want to write a query to write data as customer id, credit, debit,final_amount

where as final amount = credit-debit

Can someone please help me with query?

Welcome to Stack Overflow! We are a community of volunteers offering aid to programmers stuck on a particular task. We are not a service to write free code for you. Please read [What topics can I ask about here?](http://stackoverflow.com/help/on-topic) and [How do I ask a good question?](http://stackoverflow.com/help/how-to-ask) — Sloan Thrasher, Apr 30 '18 at 10:02
I know that I have to use inner join. somehow not able to figure how. — Thoughtful Monkey, Apr 30 '18 at 10:02
Or just sit around while GL does everything for you, as usual. — Strawberry, Apr 30 '18 at 10:02
@Strawberry Gordon needs those points. Unless its actually an AI program created by Stack Overflow. — Tim Biegeleisen, Apr 30 '18 at 10:03
@Strawberry: LOL. Must be final exam time. Seen the same 5 or 6 questions (st least the task they're trying to do) fairly frequently lately. — Sloan Thrasher, Apr 30 '18 at 10:04

score 2 · Accepted Answer · answered Apr 30 '18 at 10:02

You can use conditional aggregation:

select customerid, 
       sum(case when type = 'credit' then amount else 0 end) as credit,
       sum(case when type = 'debit' then amount else 0 end) as debit,
       sum(case when type = 'credit' then amount
                when type = 'debit' then - amount
                else 0
           end) as credit
from t
group by customerid;

split one fields into multi columns based value in other field

1 Answers1