SQL: Daily Average of Logins Per Hour
- by jerrygarciuh
This query is producing counts of logins per hour:
SELECT DATEADD(hour, DATEDIFF(hour, 0, EVENT_DATETIME), 0),
COUNT(*)
FROM EVENTS_ALL_RPT_V1
WHERE EVENT_NAME = 'Login'
AND EVENT_DATETIME >= CONVERT(DATETIME, '2010-03-17 00:00:00', 120)
AND EVENT_DATETIME <= CONVERT(DATETIME, '2010-03-24 00:00:00', 120)
GROUP BY DATEADD(hour, DATEDIFF(hour, 0, EVENT_DATETIME), 0)
ORDER BY DATEADD(hour, DATEDIFF(hour, 0, EVENT_DATETIME), 0)
...with lots of results like this:
Datetime COUNT(*)
----------------------------------
2010-03-17 12:00:00.000 135
2010-03-17 13:00:00.000 129
2010-03-17 14:00:00.000 147
What I need to figure out is how to query the average logins per hour for a given day. Any help?
