CODEWITHSUNDEEP
phpmysqlsql
Eka RudiAnto
Eka RudiAnto

Reputation: 61

total inventory stock mysql

I have a follow up question about this question of mine if you don't mind..

count total flow inventory in and out php mysql

Table A
=======================================================
**id** | **code** | **status** | **total** | **date** |
1      | B01      | IN         | 500       |2013-01-15|
2      | B01      | OUT        | 100       |2013-01-20|
3      | B01      | OUT        | 200       |2013-02-01|
4      | B01      | IN         | 300       |2013-02-05|

from the table above I would like the output like this..

Table A
===================================================================================
**id** | **code** | **status** | **total** | **date** | **stock** | 1st month stock
1      | B01      | IN         | 500       |2013-01-15| 500       | -
2      | B01      | OUT        | 100       |2013-01-20| 400       | 500
3      | B01      | IN         | 200       |2013-02-01| 600       | 400
4      | B01      | OUT        | 300       |2013-02-05| 300       | 600
5      |          |            |           |          |           | 300

how can I achieve that using mysql ? is there a way?

for the stock column I'm already achieve that using this method 

select t.*, @stock := @stock + case when status = 'IN' 
                                    then total
                                    else -total  
                               end as stock
from your_table t
cross join (select @stock := 0) s
order by t.id

from my previous question, help me I'm really new to mysql

Upvotes: 0

Views: 1365

Answers (1)

spencer7593
spencer7593

Reputation: 108370

Based on what you already have, you could add another expression to the SELECT list,

select t.*, @stock AS `1st month stock`, @stock := ...

N.B. the return of the current value of @stock would need to come BEFORE a new value is assigned to @stock in the SELECT list, so the order of the columns is slightly different than shown in the desired output.

Based on your current query, just add a new expression to your SELECT list, to return another column, e.g.

select t.*
     , @stock AS `1st month stock`
     , @stock := @stock + case when status = 'IN' 
                                then total
                                else -total  
                           end as stock
from your_table t
cross join (select @stock := 0) s
order by t.id

Upvotes: 1

Related Questions