Add and Substract one Row value to another row value in same table using mysql-CodePudding

Below is my table tbl_wallet. I want to add and subtract amount in same table addition and subtraction on the basis of credit and debit key

sno	w_userid	w_amount	w_type
1	263	100	credit
2	263	200	credit
3	263	100	credit
4	263	100	debit

Below is my query for getting detail

SELECT wl.w_userid
       , wl.w_type
       , wl.w_amount
       , DATE_FORMAT(w_date,' %D %M %Y %r') AS w_date
       , SUM(w_amount) 
FROM  tbl_wallet AS wl 
WHERE wl.w_userid=263 
ORDER BY wl.w_date DESC;

But i want the output like Below

sno	w_userid	w_amount	w_type	Balance
1	263	100	credit	100
2	263	200	credit	300
3	263	100	credit	400
4	263	100	debit	300

CodePudding user response：

SELECT *,
       SUM(CASE w_type WHEN 'credit' THEN w_amount
                       WHEN 'debit'  THEN -w_amount
                       ELSE 0 
                       END) OVER (PARTITION BY w_userid
                                  ORDER BY sno ASC) AS Balance
FROM source_table