CODEWITHSUNDEEP
sql-serverdatabaset-sql
Ben Ford
Ben Ford

Reputation: 1394

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!

Upvotes: 1

Views: 810

Answers (1)

t-clausen.dk
t-clausen.dk

Reputation: 44326

select EventType, 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), EventType
order by hour

Edit:

New solution for the changed question:

select DATEADD(hh, (DATEDIFF(hh,0,Time)),0) as 'hour', count(*) as 'apploads', 
sum(case when EventType = 1 then 1 else 0 end) EventType1,
sum(case when EventType = 2 then 1 else 0 end) EventType2
from PlaySessionEvent 
where EventType = 0 
group By DATEDIFF(hh,0,Time)
order by hour

Here is a slightly different way of writing it:

select DATEADD(hh, (DATEDIFF(hh,0,Time)),0) as [hour], 
       COUNT(*) [apploads], 
       COUNT(case when EventType = 1 then 1 end) EventType1,
       COUNT(case when EventType = 2 then 1 end) EventType2
from PlaySessionEvent 
where EventType = 0 
group By DATEDIFF(hh,0,Time)
order by hour

Upvotes: 4

Related Questions