Multiple Select Subquery Count based on Hour of Day, Would Like to Add Table/Column

Question

Right now, I have a multiple select subquery that is grabbing data based on hour of the day that's a count. What I want to do now, is to introduce another table into that query, and count based on an id as well as the datetime in the original table.

What I have right now is:

select
(
select     count(a_date)
from     t1
where     d_date
     between     '2013-01-07 00:00:00' and '2013-01-07 00:59:59'
) AS     '00:00 to 00:59',
(
select     count(a_date)
from     t1
where     d_date
     between     '2013-01-07 01:00:00' and '2013-01-07 01:59:59'
) AS     '01:00 to 01:59'

and so on, till the end of the day.

I have another query that's giving me the count based on the id and datetime, but there's only two columns, one which is showing the c_name and the other showing the count for the hour.

Ex.

select  t2.c_name, count(t1.a_date)
from    t2 join t1
on t2.t1_key = t1.t2_key
where   t1.d_date
    between '2013-01-07 00:00:00' and '2013-01-07 00:59:59'
group by    t2.c_id

Basically, I'd like to combine these two queries into one that can show the c_name and all of the hours of the day.

Any suggestions?

Answer 1

I would look into using the CASE statement.

Try something like this (adding your additional 23 columns):

select  c_name, 
   SUM(case when HOUR(d_date) = 0 then 1 else 0 end) '00:00 to 00:59',
   SUM(case when HOUR(d_date) = 1 then 1 else 0 end) '01:00 to 01:59'
from   t2
    join t1 on t2.t1_key = t1.t2_key 
group by c_name

And here is the SQL Fiddle.

You just need to add your WHERE criteria for d_date -- something like:

where  d_date between '2013-01-07 00:00:00' and '2013-01-07 23:59:59'

or

where  Date(d_date) = '2013-01-07'

Good luck!