group by hour of the day ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

group by hour of the day ?

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-08-21 : 11:15:12

Hi,

I have a log table that records certain events. I'd like to be able to run a query on it to find out which are the most "active" hours of the day.

How can I do this?

I'd like the data to look something like this

HourOfDay / totalViolations
0 / 0
1 / 3
2 / 8
3 / 3
4 / 9
5 / 15

etc on to 23

Any help is much appreciated !

Thanks,
mike123

CREATE TABLE [dbo].[tblSpamReports_KeyWord_Violations](
[spamReportID] [int] IDENTITY(1,1) NOT NULL,
[offendingID] [int] NOT NULL,
[dateViolated] [datetime] NOT NULL
) ON [PRIMARY]

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-08-21 : 11:23:19

[code]
SELECT
[HourOfDay] = DATEPART(hour,yourColumn)
,[Violations] = sum(violations)
FROM
yourTable
GROUP BY DATEPART(hour,yourColumn)
ORDER BY HourOfDay[/code]

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources