asked on

Count time of day at which records are created SQL

I have a table where records are made throughout the day and night. I want to construct a query that will return on average how many records are made at each hour.

How could I do this?

I have got as far as extracting the hour from the datetime field. But when I try and add a count/group the query breaks.

select
DATEPART(hour,DateTime) as 'Hour' from [mytable]

ASKER CERTIFIED SOLUTION

Dirk Haest

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mayankagarwal

If you want the average the query can be:

select avg(select count(*)
from [mytable]
group by DATEPART(hour,DateTime)) from dual

John Claes

The following is my suggestion

1) You need to Count the occurenced by Day and Hour
2) those Counts by the Hour schould then be used in your Average calculation

select Hour, AVG(HourSumming) as HourAverage
from
(
      select
            convert(nvarchar(10), date) as 'DATE' ,
            DATEPART(hour,date) as 'Hour' ,
            count(*) as HourSumming
      from [mytable]
      group by       convert(nvarchar(10), date),
            DATEPART(hour,date)
) as CountedByHourAndDate
group by Hour

regards
poor beggar

nhmedia

ASKER

Thanks very much for the suggestions...

I got the first solution to work! I was also interested in the average idea, but the second solution produced an error: avg function requires one argument.

nhmedia

ASKER

Very helpful, worked straight away