How do i do running totals from second column

Question

I have a data set like below,

Lot Size    Reported QTY    Qty Balance
150          100            
150          100            
150          80             
150          80            
150          5              

The Qty Balance needs to calculated as follows,

Row 1 = Lot Size - Reported Qty (row1) => 150-100 = 50
Row 2 = Reported Qty (row1) - Reported Qty(row2) => 100-100 =0
Row 3 = Reported Qty (row2) - Reported Qty(row3) => 100-80 =20
... till the last row

My expected result is

Lot Size    Reported QTY    Qty Balance
150          100            50
150          100            0
150          80             20
150          80             0
150          5              75

How do I achieve this in a query?

Answer 1

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE lots ( Lot_Size, Reported_QTY ) AS
          SELECT 150, 100 FROM DUAL
UNION ALL SELECT 150, 100 FROM DUAL
UNION ALL SELECT 150,  80 FROM DUAL
UNION ALL SELECT 150,  80 FROM DUAL
UNION ALL SELECT 150,   5 FROM DUAL;

Query 1:

SELECT Lot_Size,
       Reported_QTY,
       COALESCE( LAG( Reported_QTY ) OVER ( ORDER BY NULL ) - Reported_QTY,
                 Lot_Size - Reported_QTY ) AS Qty_Balance
FROM   Lots

Results:

| LOT_SIZE | REPORTED_QTY | QTY_BALANCE |
|----------|--------------|-------------|
|      150 |          100 |          50 |
|      150 |          100 |           0 |
|      150 |           80 |          20 |
|      150 |           80 |           0 |
|      150 |            5 |          75 |