Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Calculate running total in SQL Server 2012

Suggested Videos
Part 109 - Row_Number function in SQL Server
Part 110 - Rank and Dense_Rank in SQL Server
Part 111 - Difference between rank dense_rank and row_number in SQL



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.
running total sql server

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

calculate running total in SQL Server 2012

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

running total column

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.

2 comments:

  1. Hi Venkat

    can you explain about sequence

    ReplyDelete
  2. How do we do in 2008 , Can you please explain the same query in below versions of 2012.

    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.