sql - Mysql Case - Count Fields thats fall in curtain hours -

i trying set report has following fields :

pretty need report show date, total records fall in date (so group date) , on per hour basis 12 hour work day (from 8am - 8pm) need count when records present within times. after brain storming , brain farts thought why not use case. have tried :

 select          date_format(signintime, '%b %d, %y') date,          count(session_id) total,         sum(case when hour(signintime) > 08 , hour(signintime) < 09 1 else 0 end) '8am-9pm',         sum(case when hour(signintime) > 09 , hour(signintime) < 10 1 else 0 end) '9am-10am',         sum(case when hour(signintime) > 10 , hour(signintime) < 11 1 else 0 end) '10am-11am',         sum(case when hour(signintime) > 11 , hour(signintime) < 12 1 else 0 end) '11am-12pm',         sum(case when hour(signintime) > 12 , hour(signintime) < 13 1 else 0 end) '12pm-1pm',         sum(case when hour(signintime) > 13 , hour(signintime) < 14 1 else 0 end) '1pm-2pm',         sum(case when hour(signintime) > 14 , hour(signintime) < 15 1 else 0 end) '2pm-3pm',         sum(case when hour(signintime) > 15 , hour(signintime) < 16 1 else 0 end) '3pm-4pm',         sum(case when hour(signintime) > 16 , hour(signintime) < 17 1 else 0 end) '4pm-5pm',         sum(case when hour(signintime) > 17 , hour(signintime) < 18 1 else 0 end) '5pm-6pm',         sum(case when hour(signintime) > 18 , hour(signintime) < 19 1 else 0 end) '6pm-7pm',         sum(case when hour(signintime) > 19 , hour(signintime) < 20 1 else 0 end) '7pm-8pm'  session   session.status = '3'  group hour(signintime), date; 

if notice picture above, total 1 entire day (apr 19, 2013) if notice times (8am - 8pm) zeroed out. unsure turn / debug this. set of eyes can moving along.

regards.

you should not need use both > , < result, can check hour value:

select date_format(signintime, '%b %d, %y') date,    count(session_id) total,   sum(case when hour(signintime) = 08 1 else 0 end) '8am-9am',   sum(case when hour(signintime) = 09 1 else 0 end) '9am-10am',   sum(case when hour(signintime) = 10 1 else 0 end) '10am-11am',   sum(case when hour(signintime) = 11 1 else 0 end) '11am-12pm',   sum(case when hour(signintime) = 12 1 else 0 end) '12pm-1pm',   sum(case when hour(signintime) = 13 1 else 0 end) '1pm-2pm',   sum(case when hour(signintime) = 14 1 else 0 end) '2pm-3pm',   sum(case when hour(signintime) = 15 1 else 0 end) '3pm-4pm',   sum(case when hour(signintime) = 16 1 else 0 end) '4pm-5pm',   sum(case when hour(signintime) = 17 1 else 0 end) '5pm-6pm',   sum(case when hour(signintime) = 18 1 else 0 end) '6pm-7pm',   sum(case when hour(signintime) = 19 1 else 0 end) '7pm-8pm' session session.status = '3' group date_format(signintime, '%b %d, %y'); 

see sql fiddle demo