How do I write some SQL that will return results showing the number of rows that occured in each hourly period?

Question

Hi,

I need to produce a SQL report showing the number of times a particular event happened in each hourly period during the day. My table has a date/time column on it containing the time the event occurred.

How do I do a count of the number of rows that fall within each each hourly period during the day?

So I need to see output like this...

10:00 - 11:00 12 times

11.00 - 12:00 53 times

12:00 - 13:00 5 times etc

I'm guessing it would be a Group By, but how do you group by each hour? Thanks in advance.

Answer 1

SELECT DATEPART(hh, DateTimeColumn), COUNT(*)
FROM
    TableName
GROUP BY
    DATEPART(hh, DateTimeColumn)
ORDER BY
    DATEPART(hh, DateTimeColumn)

Answer 2

Seans solution will only work with 24 hours worth of data as datepart dd only returns 0-23.

If you need to process more than that, you'll need to add in the day too.

Something like:

SELECT CAST(DateTimeColumn AS INT) [day],DATEPART(hh, DateTimeColumn), COUNT(*)
FROM
    TableName
GROUP BY
    CAST(DateTimeColumn AS INT),
    DATEPART(hh, DateTimeColumn)
ORDER BY
    CAST(DateTimeColumn AS INT),
    DATEPART(hh, DateTimeColumn