Wednesday 23 November 2016

Calculating the running Average in sql server

See the running Average
Number
Running total
Running Average
10
10
10
5
15
7.5
20
35
11.67
15
50
12.5
5
55
11
10
65
10.83

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,
AVG(Salary) OVER (ORDER BY EmpID) AS [Running Average]
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,
AVG(salary) over (partition by gender order by empid)
as [Running Average]
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,
AVG(salary) over (partition by City order by empid)
as [Running Average]
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 AVG(e1.Salary) from employee e1 where e1.EmpID<=e.EmpID)
as [Running Average]
from employee e
order by e.EmpID

See the output
 
Hope this will be helpful J

Popular Posts