varun joshi varun joshi - 6 months ago 7
SQL Question

How to add and subtract data from one table with different where condtion

I have a three tables one is Userregistration,second is withdraw and third is walletbalance from walletbalance table i have to add all credited balance and debited balance and then subtract credited balance from debited balance for actual balance and retrieve data from all the three tables.Below are my table structure.

Userregistration table

........................................
id fullname mobile_no email
.........................................
5 varun 12344567 abc@gmail
6 nitin 12345678 def@gmail


withdraw

...............................
wid userid withdraw_status
...............................
1 5 pending
2 6 pending


walletbalance

..........................................
id user_id balance transaction_type
..........................................
1 5 100 credit
2 5 20 debit
3 6 200 credit
4 6 100 debit


I want this output:

.................................................................
wid user_id balance withdraw_status fullname mobile_no email
.................................................................
1 5 80 pending varun 12344567 abc@gmail
2 6 100 pending nitin 12344567 def@gmail


I have tried this for find actual balance of user but i was unable to
ahieve this

SELECT SUM(`balance`) as b1 from walletbalance WHERE `user_id`='5' and `transaction_type`='credit' UNION SELECT SUM(`balance`) as b2 from walletbalance WHERE `user_id`='5' and `transaction_type`='debit'

Answer
SELECT t2.wid, t2.userid AS user_id, t3.balance, t2.withdraw_status,
    t1.fullname, t1.mobile_no, t1.email    
FROM Userregistration t1
INNER JOIN withdraw t2
    ON t1.id = t2.userid
INNER JOIN
(
    SELECT user_id,
        SUM(CASE WHEN transaction_type = 'credit'
                 THEN balance
                 ELSE balance * -1
            END) AS balance
    FROM walletbalance
    GROUP BY user_id
) t3
    ON t1.id = t3.user_id
Comments