Calculate average for each day in sql server

Question

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

Answer 1

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

Answer 2

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).