Running sum/ running total using TSQL

What is running sum ?

Running sum is basically sum of all previous rows till current row for a given column. The rows can be ordered/indexed on certain condition while collecting the sum. 

Below is the example where we collect running sum based on transaction time (datetime field) 

On Running_Sum column you can notice that its sum of all rows for every row.

How can you obtain running sum in SQL ?

Using SQL windowing function 

We will create a table with transaction data as shown above and try to obtain running sum. 

Syntax for running total  

SUM(<COLUMN>) OVER (PARTITION BY <COLUMN(S)> ORDER BY <COLUMN(S))

Since in our example we don't have any group to get running total so we will not use PARTITION BY. 

Lets jump into example 

We have created a table variable and added values and we are ready with data to be validated.

Below you can copy the code for above example. 

DECLARE @Running_Total_Example TABLE

(

transaction_date DATE,

transaction_amount INT

)

INSERT INTO @Running_Total_Example (transaction_amount,transaction_date)

VALUES (10,'2021-01-01'),(15,'2021-01-02'),(12,'2021-01-03'),(13,'2021-01-04'),(15,'2021-01-05')

SELECT *,SUM(transaction_amount) OVER(ORDER BY transaction_date) AS Running_SUM FROM @Running_Total_Example ORDER BY transaction_date

Running sum by correlated subquery

We can obtain the running total using correlated subquery. We use the query if we do not have option of windowing function/clause to get running sum. 

You can copy the code from below. 

DECLARE @Running_Total_Example TABLE

(

transaction_date DATE,

transaction_amount INT

)

INSERT INTO @Running_Total_Example (transaction_amount,transaction_date)

VALUES (10,'2021-01-01'),(15,'2021-01-02'),(12,'2021-01-03'),(13,'2021-01-04'),(15,'2021-01-05')

SELECT *,(SELECT SUM(transaction_amount) FROM @Running_Total_Example WHERE transaction_date <= RT.transaction_date) Running_SUM 

FROM @Running_Total_Example RT