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
Comments
Post a Comment