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.

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
http://csharp-video-tutorials.blogspot.com/2015/10/calculate-running-total-in-sql-server.html

Slides
http://csharp-video-tutorials.blogspot.com/2015/10/calculate-running-total-in-sql-server_3.html

All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html

All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists