Get average per day / hour without group
In one table, let's say I have a log of mileage and timestamps. I want to get the average mileage per day and hour. I cannot use the inherent "Group By" clause because of the date format.
Here are 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 look like this ( PER DAY ) (note the date format doesn't matter):
Date Average
------------------------------------------------
08/11/2014 60
08/12/2014 90
08/13/2014 70
An ideal result set would look like this ( PER HOUR ) (note the date format does not 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 oversimplified and does not necessarily reflect the exact criteria required for real-world implementation. It's easy to push my own learning, because all the examples I found for things like this were extremely complex, making learning difficult.
source to share
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 watch 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;
source to share
I think this should work for the "day" version:
select cast(f_mileagetimestamp as date), avg(f_mileage)
from tb_mileage
group by cast(f_mileagetimestamp as date)
order by cast(f_mileagetimestamp as date);
For an hour, I would just use the function:
select cast(f_mileagetimestamp as date), datepart(hour, f_mileagetimestamp), avg(f_mileage)
from tb_mileage
group by cast(f_mileagetimestamp as date), datepart(hour, f_mileagetimestamp)
order by cast(f_mileagetimestamp as date), datepart(hour, f_mileagetimestamp);
source to share