Get average value per day/hour without Group By
Solution 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;
Solution 2
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 the 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);
Beems
Updated on June 09, 2022Comments
-
Beems almost 2 years
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.
-
Beems over 9 yearsSQL Server tells me that: Msg 195, Level 15, State 10, Line 1 'date' is not a recognized built-in function name.
-
peter.petrov over 9 years@Beems Next time specify that it's SQL Server in your question. And specify the version too.
-
Beems over 9 yearsI apologize, I tagged as T-SQL thinking that was enough. I forgot people would come here based on just the "SQL" tag.
-
Gordon Linoff over 9 years@Beems . . . I missed the tsql tag, my fault. In any case, I just updated the syntax.
-
Beems over 9 yearsThanks, that works like a champ. This place never ceases to amaze me with fast answers.
-
Beems over 9 yearsThanks Peter. The "By Day" query makes complete sense. The "By Hour" one, on the other hand, generates an error that I can't wrap my head around: Msg 164, Level 15, State 1, Line 4 Each GROUP BY expression must contain at least one column that is not an outer reference.
-
peter.petrov over 9 yearsI fixed the other one. Isn't it OK too?
-
Beems over 9 yearsI'm sorry, pressing "ENTER" on the keyboard added the comment rather than wrapping a new line. I wasn't finished with my comment when it submitted.
-
peter.petrov over 9 yearsSounds interesting, let me try it.
-
Beems over 9 yearsIt works now, thank you! The query seen above now does indeed work flawlessly. Indeed, the date format is exceptional. I can't say I quite understand yet how the date is concatenated, but I'll work through what you have and play with other examples on my own. Thank you, this is great.