I have a customer table like this:
trans-id | cust-id | trans-type | amount
----------------------------------------
t1 | c1 | buy | 700
t2 | c2 | sell | 200
t3 | c1 | sell | 400
t4 | c3 | buy | 900
t5 | c2 | sell | 100
I am trying to group up each of this customer with each of their type and amount. Below is the expected table that I want to get.
cust-id | num_of_trans | num_of_buy | num-of-sell | total_amount | total_buy | total_sell|
------------------------------------------------------------------------------------------
c1 | 2 | 1 | 1 | 1100 | 700 | 400
c2 | 2 | 0 | 2 | 300 | 0 | 300
c3 | 1 | 1 | 0 | 900 | 900 | 0
I already try to use 'group by', but i can only get num_of_trans and total_amount. I have no idea how to split them as buy and sell. Anyone can help me? Thanks in advance.
