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.

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.

CodePudding user response：

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.