CODEWITHSUNDEEP
phpmysql
varun joshi
varun joshi

Reputation: 481

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'

Upvotes: 1

Views: 29

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521674

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

Upvotes: 1

Related Questions