Running total to the previous row

Question

I need some help with windowing functions. I know you can calculate the sum within a window and the running total within a window. But is it possible to calculate the previous running total i.e. the running total not including the current row?

I assume you would need to use the ROW or RANGE argument. I know there is a CURRENT ROW option but I would need CURRENT ROW - 1, which is invalid syntax. My knowledge of the ROW and RANGE arguments is limited so any help would be gratefully received.

I know that there are many solutions to this problem, but I am looking to understand the ROW, RANGE arguments and I assume the problem can be cracked with these. I have included one possible way to calculate the previous running total but I wonder if there is a better way:

USE AdventureWorks2012

SELECT s.SalesOrderID
    , s.SalesOrderDetailID
    , s.OrderQty
    , SUM(s.OrderQty) OVER (PARTITION BY  SalesOrderID) AS RunningTotal
    , SUM(s.OrderQty) OVER (PARTITION BY  SalesOrderID 
                         ORDER BY SalesOrderDetailID) - s.OrderQty AS PreviousRunningTotal
    -- Sudo code - I know this does not work
    --, SUM(s.OrderQty) OVER (PARTITION BY  SalesOrderID 
    --                   ORDER BY SalesOrderDetailID
    --                   ROWS BETWEEN UNBOUNDED PRECEDING 
    --                                   AND CURRENT ROW - 1) 
    -- AS  SudoCodePreviousRunningTotal
FROM Sales.SalesOrderDetail s
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY s.SalesOrderID
    , s.SalesOrderDetailID 
    , s.OrderQty

Answer 1

The answer is to use 1 PRECEDING, not CURRENT ROW -1. So, in your query, use:

    , SUM(s.OrderQty) OVER (PARTITION BY  SalesOrderID 
                            ORDER BY SalesOrderDetailID
                            ROWS BETWEEN UNBOUNDED PRECEDING 
                                     AND 1 PRECEDING) 
    AS  PreviousRunningTotal

---

Also note that on your other calculation:

    , SUM(s.OrderQty) OVER (PARTITION BY  SalesOrderID
                            ORDER BY SalesOrderDetailID) ...

SQL-Server uses the default ^* RANGE UNBOUNDED PRECEDING AND CURRENT ROW. I think that there is an efficiency difference and ROWS UNBOUNDED PRECEDING AND CURRENT ROW is to be preferred (after testing of course and if it gives the results you want).

Much more details you can find in the blog article by @Aaron Bertrand, including performance tests: Best approaches for running totals – updated for SQL Server 2012

^{* this is of course the default range when an ORDER BY is present inside the OVER clause - otherwise, without ORDER BY the default is the whole partition.}