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
