T-SQL: Create a running total

Often when writing sql statements, you need to create a running total of a column. This is very easily done using the OVER clause.

Previously I have shown you how to create a running total when using SSRS: SSRS: Create a running total using RunningValue. Now I will show you how to do it using standard T-SQL.

In the query below I have AccountID and TransactionAmount. I want to get a running total by AccountID.

Now I change the query and add a column to use the OVER clause.

The OVER clause, when used in conjunction with the SUM function, allows us to perform a running total of the transaction. Within the OVER clause, the PARTITION BY clause is specified to restart the calculation every time the AccountId value changes. The ORDER BY clause is specified to determine in which order the rows should be calculated.

SELECT TransactionID,AccountID, TransactionType,TransactionDate,TransactionAmount,
RunningTotal= SUM(TransactionAmount) OVER ( PARTITION BY AccountId ORDER BY TransactionDate)
FROM transactions
where TransactionDate>'2008-01-02'
ORDER BY accountid,
transactiondate

Above you can see there is now a running total by AccountID. When the AccountID changes, the RunningTotal column resets and creates a new running total for the new AccountID.

Below I exported the data into Excel to validate that the numbers actually are correct. They do match the query!

What if you just want to get a running total not broken down/separated by AccountID? All you need to do is remove the “PARTITION by AccountID” section of the OVER clause. This means it will not reset the RunningTotal after each change in AccountID.

I again export the data to Excel to prove the numbers and everything ties like it should. I can feel confident in my use of the OVER clause.

There are plenty of other uses for the OVER clause and here is the link to the documentation to further your knowledge. https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15