David David - 5 months ago 24
MySQL Question

SQL : ADD & MINUS based on Field type

I have a table that look like below:

tableA

ID | BALANCE | ACTION_QTY | ACTION_TYPE |
----------------------------------------------------
1 | 0 | 3 | ADD |
2 | 0 | 2 | DEDUCT |
3 | 0 | 6 | ADD |
4 | 0 | 3 | ADD |
5 | 0 | 1 | DEDUCT |
6 | 0 | 7 | ADD |
7 | 0 | 8 | DEDUCT |
8 | 0 | 3 | ADD |
9 | 0 | 7 | DEDUCT |
10 | 0 | 9 | ADD |


SQL FIDDLE

How do i update the BALANCE field based on sum and deduct of ACTION_TYPE field and ACTION_QTY field

the result that i want is like below:

ID | BALANCE | ACTION_QTY | ACTION_TYPE |
----------------------------------------------------
1 | 3 | 3 | ADD |
2 | 1 | 2 | DEDUCT |
3 | 7 | 6 | ADD |
4 | 10 | 3 | ADD |
5 | 9 | 1 | DEDUCT |
6 | 16 | 7 | ADD |
7 | 8 | 8 | DEDUCT |
8 | 11 | 3 | ADD |
9 | 4 | 7 | DEDUCT |
10 | 13 | 9 | ADD |


any help would be great.

Answer

You can use a variable to hold the cumulative amount:

SELECT ID, 
       @s := IF(ACTION_TYPE='ADD', @s + ACTION_QTY, @s - ACTION_QTY) AS BALANCE,
       ACTION_QTY,
       ACTION_TYPE
FROM tableA
CROSS JOIN (SELECT @s := 0) AS var
ORDER BY ID 

The above query assumes that there are only two types of ACTION_TYPE values, namely 'ADD' and 'DEDUCT'. Hence, if ACTION_TYPE is not equal to 'ADD', then it is equal to 'DEDUCT'.

Demo here