I am trying to build a query in MYSQL that get the records for today, and then for each hour work out how many records there are, and there elapsed time.
Example data:
Day Time Elapsed time
2015-01-28 9:00 0:53
2015-01-28 9:51 0:07
2015-01-28 10:32 1:45
2015-01-28 11:53 0:25
Expected results after the query
Time Count Elapsed
7 0 0:0
8 0 0:0
9 2 1:00
10 1 1:45
11 1 0:25
12 0 0:0
13 0 0:0
14 0 0:0
15 0 0:0
16 0 0:0
17 0 0:0
so in this we have Each hour between 7 - 17 and how many records there are and the elapsed time for that hour.
I currently have this query:
SELECT Date,
Count(Date) AS Count,
Sum(Elapsed) / 60 / 60 AS Elapsed,
avg(Elapsed) / 60 as average
FROM
telephone
WHERE
StartExt = 'E227'
AND EndExt = 'E227'
AND `Date` = CURDATE()
Group by Date ORDER BY Date ASC
Which gets the total and elapsed for the full day and not broken down in to each hour, how could i achieve this?
For all hours that have data you can use the hour() function:
SELECT hour(time) as hour, Count(Date) AS Count,
Sum(Elapsed) / 60 / 60 AS Elapsed, avg(Elapsed) / 60 as average
FROM telephone
WHERE StartExt = 'E227' AND EndExt = 'E227' AND `Date` = CURDATE()
Group by hour(time)
ORDER BY hour ASC
If you need to get hours with no calls, that requires extra effort. You need a list of all the hours you want and then a left join. Something like this:
SELECT h.hh as hour, Count(time) AS Count,
Sum(Elapsed) / 60 / 60 AS Elapsed, avg(Elapsed) / 60 as average
FROM (select 7 as hh union all select 8 union all select 9 union all
. . .
select 17
) h left join
telephone t
on h.hh = hour(t.time) and
StartExt = 'E227' AND EndExt = 'E227' AND `Date` = CURDATE()
Group by h.hh
ORDER BY h.hh ASC
Notice that the WHERE conditions have been moved to the ON clause for the left outer join.