Get average value per day/hour without Group By

Question

In a single table, say I have a log of mileage and timestamps. I want to get the average mileage per day and per hour. I can't use an inherent "Group By" clause because of the date format.

Here is some sample data:

  Table: tb_mileage
  ===============================================
  f_mileagetimestamp           f_mileage
  -----------------------------------------------
  2014-08-11 11:13:02.000      50                       
  2014-08-11 16:12:55.000      100      
  2014-08-11 16:55:00.000      30                
  2014-08-12 11:12:50.000      80                       
  2014-08-12 16:12:49.000      100                      
  2014-08-13 08:12:46.000      40                       
  2014-08-13 08:45:31.000      100                      

So, the ideal result set would appear as follows (PER DAY) (note, format of date doesn't matter):

  Date                 Average
  ------------------------------------------------
  08/11/2014           60
  08/12/2014           90
  08/13/2014           70

The ideal result set would appear as follows (PER HOUR) (note, format of date doesn't matter):

  Date                     Average
  ------------------------------------------------
  08/11/2014 11:00:00      50
  08/11/2014 16:00:00      65
  08/12/2014 11:00:00      80
  08/12/2014 16:00:00      100
  08/13/2014 08:00:00      70

Note that the example here is purely theoretical and simplified, and doesn't necessarily reflect the exact criteria necessary for the real-world implementation. This is merely to push my own learning, because all the examples I found to do similar things were hugely complex, making learning difficult.

Answer 1

Try this for the dates version.

select cast(t.f_mileagetimestamp as date) as dt, avg(t.f_mileage) as avg_mileage
from
tb_mileage t
group by cast(t.f_mileagetimestamp as date)
order by cast(t.f_mileagetimestamp as date) asc;

For the hours version, you can use this.

select t2.dt, avg(t2.f_mileage) as avg_mileage
from
(
    select substring(CONVERT(nvarchar(100), t1.f_mileagetimestamp, 121), 1, 13) + ':00' as dt, t1.f_mileage 
    from
    tb_mileage t1
) t2
group by t2.dt
order by t2.dt asc;