SQL: Daily Average of Logins Per Hour

Question

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?

Answer 1

You can do something like this

SELECT AVG(cnt) FROM (
   your other query
) 

But you need to name the second column cnt and maybe add a where clause that only looks at the given date.

Answer 2

This will select all the logins on the 18th of March per hour and then calculates the average.

SELECT AVG(count) FROM
(
    SELECT COUNT(*) count
    FROM EVENTS_ALL_RPT_V1
    WHERE EVENT_NAME = 'Login' AND
    EVENT_DATETIME >= CONVERT(DATETIME,'2010-03-18 00:00:00', 120) AND
    EVENT_DATETIME <= CONVERT(DATETIME,'2010-03-19 00:00:00', 120)
    GROUP BY DATEADD(hour, DATEDIFF(hour, 0, EVENT_DATETIME), 0)
    ORDER BY DATEADD(hour, DATEDIFF(hour, 0, EVENT_DATETIME), 0)
)

Answer 3

Use the AVG aggregate function:

  SELECT CONCAT(DATE_FORMAT(t.event_datetime, '%Y-%m-%d %H'), ':00:00.000') AS hr,
         COUNT(*) AS cnt,
         AVG(*) AS avg
    FROM EVENTS_ALL_RPT_V1 t
   WHERE t.event_name = 'Login'
     AND t.event_datetime BETWEEN '2010-03-24' AND '2010-03-17'
GROUP BY CONCAT(DATE_FORMAT(t.event_datetime, '%Y-%m-%d %H'), ':00:00.000')
ORDER BY hr

The ORDER BY clause can use column aliases, but GROUP BY can not.

Answer 4

For March 17th, 2010:

SELECT COUNT(*) / 24
  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-18 00:00:00', 120)