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