TSQL: Cannot perform an aggregate function AVG on COUNT(*) to find busiest hours of day

Answer 1

Using inline view:

SELECT DATEPART(hh, x.visitdate),
       AVG(x.num)
  FROM (SELECT t.visitdate,
               COUNT(*) 'num'
          FROM CUSTOMERLOG t
         WHERE t.visitdate BETWEEN 'Jan 1 2009' AND 'Aug 1 2009'
      GROUP BY t.visitdate) x
GROUP BY DATEPART(hh, x.visitdate)

Using CTE (SQL Server 2005+) equivalent:

WITH visits AS (
   SELECT t.visitdate,
          COUNT(*) 'num'
     FROM CUSTOMERLOG t
    WHERE t.visitdate BETWEEN 'Jan 1 2009' AND 'Aug 1 2009'
 GROUP BY t.visitdate)
   SELECT DATEPART(hh, x.visitdate),
         AVG(x.num)
    FROM visits x
GROUP BY DATEPART(hh, x.visitdate)

Answer 2

The number of days is known and it is equal to DATEDIFF(day,CONVERT(DATETIME,'2009.01.01',120),CONVERT(DATETIME,'2009.09.01',120)). You have to calculate sum and divide it by number of days in selected range:

SELECT  
  DATEPART(hh, VisitDate),
  CAST(COUNT(*) AS FLOAT) / DATEDIFF(day,CONVERT(DATETIME,'2009.01.01',120),CONVERT(DATETIME,'2009.09.01',120))
FROM CustomerLog
WHERE 
  (VisitDate >= CONVERT(DATETIME,'2009.01.01',120)) AND 
  (VisitDate < CONVERT(DATETIME,'2009.09.01',120))  
GROUP BY DATEPART(hh, VisitDate)

CAST(COUNT(*) AS FLOAT) to have more precise result, but you can leave just COUNT(*) and have integer result.

If you use parameters, it will be:

SELECT  
  DATEPART(hh, VisitDate),
  CAST(COUNT(*) AS FLOAT) / DATEDIFF(day,@beginningDate,@endDate)
FROM CustomerLog
WHERE 
  (VisitDate >= @beginningDate) AND 
  (VisitDate < @endDate)  
GROUP BY DATEPART(hh, VisitDate)

If you want results for January, you have to use @beginningDate = '2009.01.01', @endDate = '2009.02.01'.