Geek asks and answers

Multiple tally counting tally counting based on hours of the day, would like to add a table / column

Right now I have a multiple select subquery that captures data based on the hour of the day when it is being counted. Now I want to present another table in this query and calculate based on the identifier, as well as the date and time in the original table.

What I have now:

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'

etc., until the end of the day.

I have another query that gives me an account based on id and datetime, but there are only two columns, one of which shows the name c_name, and the other shows the number of hours.

Ref.

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 would like to combine these two queries into one, which can show c_name and all hours of the day.

Any suggestions?

+5
source share
1 answer

CASE.

- ( 23 ):

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

SQL Fiddle.

WHERE d_date - - :

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


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

!

+4

More articles:


All Articles