Monday, April 16, 2012

Selecting multiple counts grouped by time from same table

I have a table logging application activity.
Each row contains a DateTime ("Time") and an "EventType" column... (And obviously some others that are unimportant here)



I would like to be able to get a count of the number of different EventTypes that occur every hour.



I'm currently getting a basic count of a single EventType with:



select DATEADD(hh, (DATEDIFF(hh,0,Time)),0) as 'hour', count(*) as 'apploads' 
from PlaySessionEvent
where EventType = 0
Group By DATEADD(hh,(DATEDIFF(hh,0,Time)),0)
order by hour


What is the easiest way to extend this to count multiple different EventTypes within the same hour?



::Update



Should have specified, I havn't just grouped by the EventType aswell because I only want a subset of all the EventTypes available. (ie. not boring trace/debug data)
Also, I wanted the different event types as columns, rather than additional rows duplicating the DateTime entries.



Eg...



DateTime           EventType1        EventType2
12:12:12 12/12/12 45 22


Apologies for the inexact initial question!





No comments:

Post a Comment