SQL Query to count by time for particular column

Question

OK I'm using MS SQL and I have a table with values like this:

ID     Date     Radio     Time
111    4/1/09    1        3:00:00
111    4/1/09    2        4:00:00
111    4/1/09    3       14:00:00
111    4/1/09    1       15:00:00
111    4/1/09    3       16:00:00
222    4/1/09    2        2:00:00
222    4/1/09    1        5:00:00
222    4/1/09    1       19:00:00
333    4/1/09    1        3:00:00
333    4/1/09    2        5:00:00
333    4/1/09    2        13:00:00
333    4/1/09    3        17:00:00

Ok so I need a Query where:

window 1 < 12:00
13 < window 2 < 15:00
window 3 > 16:00

To return sums:

           Window1     Window2    Window3  
Radio 1:     3           0          2
Radio 2:     3           1          0
Radio 3:     0           1          2

I have tried using count() but I cannot get these to join correctly on the right 'radio' Any suggestions?

Answer 1

SELECT [Radio],
    SUM(CASE WHEN [Time] < 12 THEN 1 ELSE 0 END) AS Window1,
    SUM(CASE WHEN [Time] >= 12 AND [TIME] < 15 THEN 1 ELSE 0 END) AS Window2,
    SUM(CASE WHEN [Time] >= 16 THEN 1 ELSE 0 END) AS Window3
FROM [Table]
GROUP BY [Radio]

Adjust <= / < and >= / > as appropriate; you weren't very specific on that point.

Also, your first condition was window 1 > 12. I'm assuming you mean window 1 < 12.

Answer 2

Hi,

I may have cheated and used SQL Server 2008 using a [time(7)] datatype for the time column, which I called "thetime." This will give you the same results as you have asked for.

select radio, SUM(case when thetime <= '12:00:00' then 1 else 0 end) Window1
    , SUM(case when thetime between '13:00:00' and '15:59:59' then 1 else 0 end) Window1
    , SUM(case when thetime >= '15:00:00' then 1 else 0 end) Window3
from stackoverflow
GROUP BY radio
ORDER BY radio

Hope that helps,

Calvin