CODEWITHSUNDEEP
sql-servert-sql
FunMatters
FunMatters

Reputation: 601

Calculate average per row in SQL Server

I need to calculate the average per day from 1 st record of year for each row. I can do it by iterating in a while loop but am sure that I can achieve this simpler. I woould nee a query to calculate the avgAmount near the Amount field.

The average is simply calculated by summing the amounts and divide by the difference between min and max dates for each row.

create table a
(
date  datetime,
dept varchar(50),
amount float
)

insert into a values ('2016-02-07', 'abc', 25)
insert into a values ('2016-02-27', 'abc', 20)
insert into a values ('2016-03-09', 'abc', 30)
insert into a values ('2016-03-28', 'abc', 45)
insert into a values ('2016-04-07', 'abc', 40)
insert into a values ('2016-04-30', 'abc', 50)
insert into a values ('2016-05-07', 'abc', 60)
insert into a values ('2016-05-27', 'abc', 50)
insert into a values ('2016-06-30', 'abc', 38)
insert into a values ('2016-07-17', 'abc', 45)
insert into a values ('2016-07-30', 'abc', 55)
insert into a values ('2016-08-07', 'abc', 70)

SQLFIDDLE

Any help please?

Upvotes: 1

Views: 13069

Answers (3)

Charles Bretana
Charles Bretana

Reputation: 146429

If it's SQL Server:

Select Sum(a.amount) / datediff(day, start.Date, end.Date)
From a 
  join a start 
    on start.Date = (Select Min(Date) from a
                     Where Year(date) = @Year
                        and dept = a.Dept)
  join a end 
    on end.Date =  (Select Max(Date) from a
                     Where Year(date) = @Year
                        and dept = a.Dept)
Where Year(a.Date) = @year

Upvotes: 1

Linkan
Linkan

Reputation: 579

One way to do it is:

SELECT date, amount,
AVG(amount) OVER(ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT
ROW) AS AvAmount
FROM a
ORDER BY date

SQL Fiddle

Upvotes: 4

JohnHC
JohnHC

Reputation: 11195

Try this:

with FirstCalc as
(
select dept, min(date) as MinDate, max(date) as MaxDate, sum(amount) as TotalAmount
from a
group by dept
)
select dept, TotalAmount/datediff(dd, MinDate, MaxDate) as AvePerDay
from FirstCalc

Upvotes: 1

Related Questions