SQL quantity calculation

Question

Well, I have a mind-scratcher that I cannot solve. Total newbie :) I need to calculate stock item quantity and detect negative values if they appear in calculation:

inquantity | outquantity
100        |      0
10         |      0
0          |     50
0          |     100
20         |      0
0          |     80
15         |      0
100        |      0

And I need to calculate Quty:

inquantity | outquantity | Quty
100        |      0      | 100
10         |      0      | 110
0          |     50      | 60
0          |     100     | -40
20         |      0      | -20
0          |     80      | -100
15         |      0      | -85
100        |      0      | 15

How can i do that ?

Regarding Abhik's post:

select 
id ,
inquantity,
outquantity,
@qty:= (@qty+inquantity)-outquantity as qty 
from quantity,(select @qty:= 0 )r 
order by id;

is there a possibility to reset the variable @qty on productid change?

+----+-----------+------------+-------------+------+
| id | productid | inquantity | outquantity | qty  |
+----+-----------+------------+-------------+------+
|  1 |         1 |        100 |           0 | 100  |
|  2 |         1 |         10 |           0 | 110  |
|  3 |         1 |          0 |          50 |  60  |
|  4 |         1 |          0 |         100 | -40  |
|  5 |         2 |         20 |           0 |  20  |
|  6 |         2 |          0 |          80 | -60  |
|  7 |         2 |         15 |           0 | -45  |
|  8 |         3 |        100 |           0 | 100  |
+----+-----------+------------+-------------+------+

You can't, with the data structured as it is. SQL tables represent *unordered* sets. There is no inherent ordering, unless you have a column that specifies that ordering. Do you have a date or id with this information? — Gordon Linoff, Apr 17 '15 at 11:33
Yes I have product ID and this is an output of already filtered table:SELECT di.inquantity, di.outquantity FROM docfinitem AS di WHERE di.warehouseid=7 AND di.productid=4563 — Nick, Apr 17 '15 at 11:37
Include that product ID in your examples! (But that's not enough... How do you know the order without a timestamp or transaction number or similar?) — jarlh, Apr 17 '15 at 11:38
Possibly a duplicate of http://stackoverflow.com/questions/664700/calculate-a-running-total-in-mysql — Morvael, Apr 17 '15 at 11:41

score 2 · Accepted Answer · answered Apr 17 '15 at 11:46

Consider the following as you mentioned you have id

mysql> select * from quantity ;
+------+------------+-------------+
| id   | inquantity | outquantity |
+------+------------+-------------+
|    1 |        100 |           0 |
|    2 |         10 |           0 |
|    3 |          0 |          50 |
|    4 |          0 |         100 |
|    5 |         20 |           0 |
|    6 |          0 |          80 |
|    7 |         15 |           0 |
|    8 |        100 |           0 |
+------+------------+-------------+

We can get the desired result as

select 
id ,
inquantity,
outquantity,
@qty:= (@qty+inquantity)-outquantity as qty 
from quantity,(select @qty:= 0 )r 
order by id;

The output would be

+------+------------+-------------+------+
| id   | inquantity | outquantity | qty  |
+------+------------+-------------+------+
|    1 |        100 |           0 |  100 |
|    2 |         10 |           0 |  110 |
|    3 |          0 |          50 |   60 |
|    4 |          0 |         100 |  -40 |
|    5 |         20 |           0 |  -20 |
|    6 |          0 |          80 | -100 |
|    7 |         15 |           0 |  -85 |
|    8 |        100 |           0 |   15 |
+------+------------+-------------+------+

Not sure I understand it, provide some sample data and better with a new question. — Abhik Chakraborty, Apr 20 '15 at 07:37

score 0 · Answer 2 · answered Apr 17 '15 at 11:36

The idea is the get the cumulative sum. Assuming you have a column that provides ordering information, you can do this with subqueries or variables. The latter should be more efficient:

select t.*, (cumei - cumeo) as diff
from (select t.*, (@i := @i + inquantity) as cumei,
              (@o := @o + outquantity) as cumeo
      from table t
           (select @i := 0, @o := 0) vars
      order by id
     ) t
where (cumei - cumeo) < 0;

SQL quantity calculation

2 Answers2