# Running total to the previous row

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
``````

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.