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
e.empid,e.empname, e.gender, e.city,e.salary,
[running
total] = e.salary + coalesce(
(
select sum(e1.salary)
from employee as e1
where e1.empid < e.empid), 0
)
from employee as e
order by e.empid
|
See the output
Hope this will be helpful J
