Cummulative Total

[isnomagic]

I have created a temporary table showing inventory activity for a particular product. The table has columns showing type of transaction (purhase, transfer, sale, issue etc.), date and finally the quantity moved. The results are in date order so the activity for the quantity moved is in activity order as such.

So I have a column which starts at a beginning balance of zero, then there is an issue of stock for the stock creation in the database followed by positive and negative quantity changes depending on wether it is a stock in or stock out move.

What I am wanting is to add a new column giving cummulative total alongside each row.
I am not aware of any simple function/command that does this?
Any ideas how this can be done?

e.g. (Last two columns)
QtyMoved Cummulative
0 0
12 12
2 14
-3 11
-1 10

and so on until it reaches present date time.

 

[JarlH]

I'm not quite sure your data model is perfect. Do you really need to store that extra column (of redundant data)?

For example a view could do what you are looking for:

CREATE VIEW tr_view AS
SELECT id, QtyMoved, (SELECT SUM(QtyMoved) FROM tr AS tr2
WHERE tr2.id <= tr1.id) AS Cummulative
FROM tr AS tr1

where id is your id/timestamp column and tr is the name of you transaction table.


If you really want to store the redundant data, the following update statement will do it:

UPDATE tr
SET Cummulative = (SELECT SUM(QtyMoved) FROM tr AS tr2
WHERE tr2.id <= tr.id)