Calculate running total in SQL Server 2012

running total sql server
cumulative total in ms sql server
running total column

In this video we will discuss how to calculate running total in SQL Server 2012 and later versions.

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.

We will use the following Employees table for the examples in this video.

SQL Script to create Employees table
Create Table Employees
(
Id int primary key,
Name nvarchar(50),
Gender nvarchar(10),
Salary int
)
Go

Insert Into Employees Values (1, 'Mark', 'Male', 5000)
Insert Into Employees Values (2, 'John', 'Male', 4500)
Insert Into Employees Values (3, 'Pam', 'Female', 5500)
Insert Into Employees Values (4, 'Sara', 'Female', 4000)
Insert Into Employees Values (5, 'Todd', 'Male', 3500)
Insert Into Employees Values (6, 'Mary', 'Female', 5000)
Insert Into Employees Values (7, 'Ben', 'Male', 6500)
Insert Into Employees Values (8, 'Jodi', 'Female', 7000)
Insert Into Employees Values (9, 'Tom', 'Male', 5500)
Insert Into Employees Values (10, 'Ron', 'Male', 5000)
Go

SQL Query to compute running total without partitions
SELECT Name, Gender, Salary,
SUM(Salary) OVER (ORDER BY ID) AS RunningTotal
FROM Employees

SQL Query to compute running total with partitions
SELECT Name, Gender, Salary,
SUM(Salary) OVER (PARTITION BY Gender ORDER BY ID) AS RunningTotal
FROM Employees

What happens if I use order by on Salary column
If you have duplicate values in the Salary column, all the duplicate values will be added to the running total at once. In the example below notice that we have 5000 repeated 3 times. So 15000 (i.e 5000 + 5000 + 5000) is added to the running total at once.

SELECT Name, Gender, Salary,
SUM(Salary) OVER (ORDER BY Salary) AS RunningTotal
FROM Employees

So when computing running total, it is better to use a column that has unique data in the ORDER BY clause.

Text version of the video

Slides

All SQL Server Text Articles

All SQL Server Slides

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

[TheMordernMan]

I just love you for creating so many awesome videos. You are indirectly helping others to run their home by educating them so that they land up getting jobs. Thank you so much Kudvenkat!

[ajitkumarsahoo]

Hi, suppose we have data till 2024 then How to get the running totals of a column till latest date?

[dmsosa]

C"est Magnifique!... I thank you very much.. very clear.. The universe shall reward your talent and generosity.. .Regards.!

[RiteshKumar-xoll]

How to do running total from two tables, one table is of debit and one is credit?

[computereducationforall]

This Video plays important role in Computer education for all. i really appreciated it... keep it up

[hmvartak]

Is there any way doing the same in sql server2008 (besides cursor)?

[nagarjunakrishnamurthy]

Thanks a lot, you have made every concept very simple to understand. if you explain data partition techniques in database, would be more helpful 🙏

[accio]

I think we can use Salary as primary ordering col and then use ID as a secondary ordering col and achieve runningTotal while maintaining the order of the salary. Can't we?

[krzysztofs]

thank you for educating community ! your tutorial rules on youtube! Thank U Sir ! God bless you and keep goin' please!

[rahulshende]

Thanks a lot sir! Very detail expiations... All the doubts are cleared.

[ypwygk]

NEED HELP


Msg 102, Level 15, State 1, Line 17
Incorrect syntax near 'order'.


USE SAMPLE2
SELECT ID, NAME, GENDER, SALARY,
SUM (SALARY) OVER (PARTITION BY GENDER ORDER BY ID) AS RunningAgeTotal
FROM EMPLOYEES

[dbin]

Excellent explanation. Clear and concise. Thank you!

[PinasPiliNa]

Great tutorial. Is there a way to compute for fines for a daily amortization? eveytime the payment is delayed then compute for the fine for a grace period of 4 days. If I did not pay today, I will not be fined, but if I will not pay in 4 days I will be fined, and what if there is an advanced payment. Thanks!

[zhangzhexin]

Hi Sir,

I got an error message like below:
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'order'.

when I run below query:
SELECT Name, Gender, Salary, SUM(Salary) OVER (ORDER BY id) AS RunningTotal
FROM Employees

I use SQL Server2016, is this caused by the version of SSMS?

[archrodney]

-- Here is my code to achieve the same thing with a cursor:


select *, sum(Salary) over (order by ID) as RunningTotal,
NULL as RunningTotalWithCursor into #EmployeesWithRunningTotal from Employees

declare RunningTotalCursor cursor for
select Salary from Employees

open RunningTotalCursor
declare @runningTotal int
declare @temp int set @temp = 0
declare @id int set @id = 1

fetch next from RunningTotalCursor into @runningTotal
begin
set @temp = @temp + @runningTotal
update #EmployeesWithRunningTotal set RunningTotalWithCursor = @temp where ID = @id
set @id = @id + 1
fetch next from RunningTotalCursor into @runningTotal
end
close RunningTotalCursor

select * from #EmployeesWithRunningTotal

deallocate RunningTotalCursor
drop table #EmployeesWithRunningTotal

[rafaelsantana]

Hey, imagine you have an Employee (id = 1 and salary = 2000) and another Employee (id =. 2 and salary = 2000).. That problem with duplicate salary will happen even using order by Id, right? Regards.

[ArvindVerma-xcnt]

Without using the inbuilt function:


select *, null total into #temp from employees
declare @running int

set @running=0

update #temp

select * from #temp

[DanielWillen]

I'm guessing you can use ROW_NUMBER() OVER (order by supplierid) instead of a primary key?

[phanikumar]

Sir, please upload Performance tunning videos..

[yambasnet]

is it possible to create a table with running total column after salary column
i mean when we insert data then running total column get auto values which are coming from salary column with the auto cumulative total