CODEWITHSUNDEEP
sqldatabaseoracle-database
Kasun Gamage
Kasun Gamage

Reputation: 346

How to merge(with getting sum) some duplicate data in sql table?

After following some queries, I got below result on my oracle SQL developer. figure 1

But I need to get it like this :

[figure 2]

I want to merge it with getting sum in Cash_IN and Cash_out for each User_ID.

This is the sql query I use to get upper(first figure) result : 

SELECT User_ID, Cash_In, Cash_out 
FROM (SELECT wa.User_ID, count(tt.ID) Cash_In, 0 Cash_out 
      FROM mwt_wallet_transactions t, mwt_txn_types tt, mwt_user_wallet wa
       WHERE t.txn_code = tt.ID and
        t.a_number = wa.id and 
        tt.ID = '1'
        GROUP BY wa.User_ID)

UNION ALL

SELECT User_ID, Cash_In, Cash_out 
FROM (SELECT wa.User_ID, 0 Cash_In, count(tt.ID) Cash_out 
      FROM mwt_wallet_transactions t, mwt_txn_types tt, mwt_user_wallet wa
      WHERE t.txn_code = tt.ID and 
        t.a_number = wa.id and 
        tt.ID = '2'
      GROUP BY wa.User_ID)
ORDER BY User_ID;

Upvotes: 0

Views: 227

Answers (1)

Tatiana
Tatiana

Reputation: 1499

Try this one:

SELECT User_ID, Cash_In, Cash_out 
FROM 
(SELECT wa.User_ID, count(case when tt.ID = '1' then 1 else null end) Cash_In,
        count(case when tt.ID = '2' then 1 else null end)  Cash_out 
FROM mwt_wallet_transactions t, mwt_txn_types tt, mwt_user_wallet wa
WHERE t.txn_code = tt.ID and
t.a_number = wa.id
GROUP BY wa.User_ID)
ORDER BY mobile_no;

Upvotes: 3

Related Questions