Compute 2 Columns from current row and from the row above

I have a table that looks like this

My target is to look like this

Basically what it did is to sum all the way down based on stock in and stock out from beginning balance however I cant achieve it. How can I display this?

I hope my table will display balance without affecting the Beginning balance row

Answer

You can use window functions (if you are running MySQL 8.0). Assuming that colum id defines the ordering of rows, that would be:

select
    t.*,
    sum(coalesce(balance, 0) + coalesce(stock_in, 0) - coalesce(stock_out, 0))
        over(partition by branch_id order by id) new_balance
from mytable t