• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

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]
0
nhmedia
Asked:
nhmedia
1 Solution
 
DhaestCommented:
Did you try

select
      DATEPART(hour,DateTime) as 'Hour' , count(*)
from [mytable]
group by DATEPART(hour,DateTime)
0
 
mayankagarwalCommented:
If you want the average the query can be:

select avg(select count(*)
from [mytable]
group by DATEPART(hour,DateTime)) from dual
0
 
John ClaesSenior .Net Consultant & Technical AnalistCommented:
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
0
 
nhmediaAuthor Commented:
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.  

0
 
nhmediaAuthor Commented:
Very helpful, worked straight away
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now