SQL Server: Average counts by hour and day of week

Question

Background I have a table set up in a SQL Server environment that contains a log of various activity that I'm tracking. Particular log items use unique codes to categorize what activity is taking place and a datetime field tracks when that activity occurred. Problem I would like to, using either a single query or a stored procedure, get an average of hourly counts of activity, grouped by day of the week. Example: ``` Day | Hour | Average Count ------------------------------- Monday | 8 | 5 Monday | 9 | 5 Monday | 10 | 9 ... Tuesday | 8 | 4 Tuesday | 9 | 3 ...etc ``` Right now I've got a query setup that spits out the counts per hour per day, but my problem is taking it a step further and getting average by day of week. Here's my current query: ``` SELECT CAST([time] AS date) AS ForDate, DATEPART(hour, [time]) AS OnHour, COUNT(*) AS Totals FROM [log] WHERE [code] = 'tib_imp.8' GROUP BY CAST(time AS date), DATEPART(hour,[time]) ORDER BY ForDate Asc, OnHour Asc ``` Any suggestions as to…

Accepted Answer

Guessing here:

```
SELECT [Day], [Hour], [DayN], AVG(Totals) AS [Avg]
FROM
  (
        SELECT 
          [Day]  = DATENAME(WEEKDAY, [time]),
          [DayN] = DATEPART(WEEKDAY, [time]),
          [Hour] = DATEPART(HOUR,    [time]),
          Totals = COUNT(*)
        FROM dbo.[log] 
            WHERE [code] = 'tib_imp.8'
        GROUP BY 
          DATENAME(WEEKDAY, [time]),
          DATEPART(WEEKDAY, [time]),
          DATEPART(HOUR,    [time])
  ) AS q
GROUP BY [Day], [Hour], [DayN]
ORDER BY DayN; 
```

Again, without data, I might once again be throwing a handful of mud at the wall and hoping it sticks, but perhaps what you need is:

```
SELECT [Day], [Hour], [DayN], AVG(Totals) AS [Avg]
FROM
(
    SELECT 
  w = DATEDIFF(WEEK, 0, [time]),
      [Day]  = DATENAME(WEEKDAY, [time]),
      [DayN] = DATEPART(WEEKDAY, [time]),
      [Hour] = DATEPART(HOUR,    [time]),
      Totals = COUNT(*)
    FROM dbo.[log] 
      WHERE [code] = 'tib_imp.8'
    GROUP BY 
  DATEDIFF(WEEK, 0, [time]),
      DATENAME(WEEKDAY, [time]),
      DATEPART(WEEKDAY, [time]),
      DATEPART(HOUR,    [time])
  ) AS q
GROUP BY [Day], [Hour], [DayN]
ORDER BY DayN; 
```

This is also going to produce integer-based averages, so you may want to cast the Totals alias on the inner query to DECIMAL(something,something).

SQL Server: Average counts by hour and day of week

Solution

Problem