Microsoft Business Intelligence: Calculating the running total in sql server

Wednesday, 23 November 2016

Calculating the running total in sql server

A running total is the summation of a sequence of numbers which is updated each time a new number is added to the sequence, by adding the value of the new number to the previous running total. See the example

Number	Running total
10	10
5	15
20	35
15	50
5	55
10	65

Let’s see who calculate the running total in sql server with the help of query

We have a table Employee.

Create Table Employee

(

EmpID int identity(1,1) primary key ,

EmpName varchar(50),

Gender varchar(10),

City varchar(30),

Salary int

)

Creating the Employee table

Now I am inserting some records.

Insert Into Employee Values ('Bagesh', 'Male','pune',5000)

Insert Into Employee Values ('Kumar', 'Male','Chennai',4500)

Insert Into Employee Values ('Prema', 'Female','Pune', 5500)

Insert Into Employee Values ('Fatima', 'Female','Mumbai',4000)

Insert Into Employee Values ('Ajit', 'Male','Patna',3500)

Insert Into Employee Values ('Mala', 'Female','patna',5000)

Insert Into Employee Values ('Manish', 'Male','pune',6500)

Insert Into Employee Values ('Kajal', 'Female','chennai',7000)

Insert Into Employee Values ('Mohan', 'Male','Mumbai',5500)

Insert Into Employee Values ('Vikash', 'Male','pune',5000)

Insert Into Employee Values ('Sonam', 'Female','Mumbai',15000)

See the table

There are multiple way to find the running total.

Using OVER clause

SELECT EmpId,EmpName, Gender, Salary,

SUM(Salary) OVER (ORDER BY EmpID) AS [Running Total]

FROM Employee

See the Output.

Suppose we want to find the running total according to the Gender use below sql script.

select empid,empname, gender, salary,

sum(salary) over (partition by gender order by empid)

as [Running Total]

from employee

See the output

Suppose we want to find the running total city wise we need to use below sql script

select empid,empname, gender, salary,City,

sum(salary) over (partition by City order by empid)

as [Running Total]

from employee

Using sub query

Second way to finding the Calculating the running sum

select e.empid,e.empname, e.gender, e.City,e.salary,

(select sum(e1.Salary) from employee e1 where e1.EmpID<=e.EmpID)

as [Running Total]

from employee e

order by e.EmpID

See the output

Using Inner join

We also get the running total

select

e.empid,e.empname, e.gender, e.city,e.salary,

[running total] = sum(e1.salary)

from

employee as e

inner join

employee as e1

on e1.empid<= e.empid

group by e.empid,e.empname, e.gender, e.city,e.salary

order by e.empid

Suppose if any value in salary is NULL then if we use above sql query they will return the NULL value. With the help of below sql query we will get the correct running total.

select