Subtracting sum of previous rows' data from current row's data in SQL

Question

I want to create a table or view in SSMS which should has a column called qoh. In that column should be calculate sum values of the column rel_qty. i.e.

1. The first data of qoh is the same of the first row's value of rel_qty.
2. Second value of qoh should be sum of first and second rows' value of rel_qty.
3. Third value of qoh should be sum of first, second and third rows' value of rel_qty.

is there any possibilities to do this?

Here is my query. but this query satisfied only first and second rows not more that that,

SELECT  a.id, a.tot_qty, a.rel_qty, 
    (a.tot_qty - 
        COALESCE (a.rel_qty +
            (SELECT b.rel_qty
        FROM    dbo.foo AS b
        WHERE   (b.id = a.id - 1)), 
        a.rel_qty)
    ) AS qoh 
FROM    dbo.foo AS a
ORDER BY    a.id

And if my id skip one value this query failed to do the calculation. How can I fix this?

here is screenshot of my result

Answer 1

It seems you need a running total.

If tot_qty is the same in all rows then you can use

SELECT  id, 
        tot_qty, 
        rel_qty, 
        tot_qty - SUM(rel_qty) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS qoh 
FROM dbo.foo
ORDER BY id;

ROWS UNBOUNDED PRECEDING is the abbreviated version of ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

This window frame-ing option means that for each row the SUM will include itself and all predecessors as ordered by id.

The ROWS option is potentially important for efficiency as described in Best approaches for running totals – updated for SQL Server 2012