CODEWITHSUNDEEP
sqlsql-server
DirkDooms
DirkDooms

Reputation: 33

Calculate average for each day in sql server

Hope someone can help me out with the following query as I am not that good in SQL.

I have a SQL table which in which each hour an number of prices for a specific product are stored, day after day. I would like to sum these prices per hour and than calculate the average of these sums over the day. And that for each day in the table.

Table looks something like this (reduced to the appropriate fields):

Date | Time | Price
01\01\2018 | 00:00 | 100
01\01\2018 | 00:00 | 100
01\01\2018 | 00:00 | 120
01\01\2018 | 01:00 | 150
01\01\2018 | 01:00 | 150
01\01\2018 | 01:00 | 90
01\01\2018 | 02:00 | 100
01\01\2018 | 02:00 | 105
01\01\2018 | 02:00 | 98
....
02\01\2018 | 00:00 | 110
02\01\2018 | 00:00 | 87
02\01\2018 | 00:00 | 126
02\01\2018 | 01:00 | 140
....

So for the example above:

1/1/2018 -> 00:00 -> 320
1/1/2018 -> 01:00 -> 390
1/1/2018 -> 02:00 -> 303

Average for that day: 337,6 (and this for each day in the table)

Hope that makes any sense.

Regards, Dirk

Upvotes: 3

Views: 21734

Answers (3)

Hasan Fathi
Hasan Fathi

Reputation: 6086

try this:

select Date, avg(price) as 'average of day'
  from
   (
    select Date, Time, sum(price) as 'price'
    from table
    group by Date,Time
   )
group by Date

Upvotes: 7

Jon Cluff
Jon Cluff

Reputation: 109

try:

select date,time,AVG(Price) as AvgPrice
from tablename
group by date,time

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can sum for the day and then divide by 24 or the number of hours:

select date, sum(price) / count(distinct time)
from t
group by date
order by date;

Or, if missing hours should count as 0, then use 24 instead of the count(distinct).

Upvotes: 0

Related Questions