Running total of SQL query

Question

I have an SQL snippet which gives me an output similar to below, except the RUNNING_TOTAL column. I'd like to add the RUNNING_TOTAL column where the the numbers adding up from the last record upwards as per the example below.

ROW NUMBER	CHANGE	INVENTORY_COUNT	RUNNING_TOTAL	DATE	USER
1	Some reason 1	31	30	Date 1	John
2	Some reason 2	-1	-1	Date 2	Kate
3	Some reason 3	1	0	Date 2	Peter
4	Some reason 4	-1	-1	Date 3	Parker

Can this be achieved in SQL? I have tried to wrap the current query with SELECT *, SUM(INVENTORY_COUNT) AS 'RUNNING_TOTAL') and had no luck.

Current SQL query:

SELECT 
 ROW_NUMBER() OVER(ORDER BY Date) ROW_NUM, 
 A.Change as 'CHANGE', 
 A.InventoryCount AS 'INVENTORY_COUNT', 
 A.Date AS 'DATE', 
 A.Users AS 'USER' 
FROM(
    SELECT 
    'Stocktake' as 'Change',
    STK.stock_count as 'InventoryCount',
    stk.time_stamp as 'Date',
    STK.count_user as 'Users'
    from IWS_STOCK_TAKE STK WHERE product_code='F100020D'

    UNION
    SELECT
    'Stock pick' as 'Change',
    -1 * abs(picks.PickedQty) as 'InventoryCount',
    picks.picked_time as 'Date',
    picks.PickedBy as 'Users'
    FROM IWS_ORDER_PICKS picks WHERE ProdCode='F100020D'

    UNION
    SELECT
    'Purchase receipt' as 'Change',
    rcv.RECEIVED_QTY as 'InventoryCount',
    rcv.RECEIVED_TIMESTAMP as 'Date',
    rcv.RECEIVER as 'Users'
    FROM IWS_PURCHASE_RECEIVED rcv where PRODUCT_CODE='F100020D'

    UNION
    SELECT
    'Stock Adjustment' as 'Change',
    ADJ.VAR 'InventoryCount',
    ADJ.PROCESSED_DATE as 'Date',
    ADJ.USER_ID as 'Users'
    FROM IWS_STOCK_TAKE_ADJUSTMENTS ADJ where PRODUCT_CODE='F100020D'

) A

Thank you for your assistance.

FYI, there's no reason to be using `UNION` here; there's no way your different datasets could return rows with the same value, as you define different *literal* values for the columns aliased as `Change`. Use `UNION ALL`. Also, don't use literal strings for aliases; it's the only place it works, it's confusing (for new and old hands) and some methods for literal string aliasing is deprecated. Use the T-SQL delimit identifier, Brackets (`[]`), or the ANSI SQL delimit identifier, double quotes (`"`). — Thom A, Sep 14 '21 at 11:14

score 1 · Accepted Answer · answered Sep 14 '21 at 11:00

You can calculate the running total using a window function. Based on the table you provided in your question:

select t.*,
       sum(total) over (order by date desc) as running_total
from t;

I'm not sure if you want to use date or row_number to define the last row. Whichever goes in the order by.

If your table is generated by a complicated query, you can use a CTE and then run this on the CTE.

Running total of SQL query

1 Answers1