want to calculate phone calls per month and indicate "0" for months with no calls ?

[WebDevGuy]

Table structure:

CALLS:
ID
incoming_from
time_of_call
date_of_call

I'm using the query below. It gives me what I want except if there are no calls for January, it skips that month, which makes perfect sense logically except that I want it to indicate "0" for january.

I want to run a query that forces a result for January even if there are no records that meet that criteria.

Is there something like a PHP explode or something like that where you can force an answer for a month not there? I'd rather not build a table for all the months/years.

Any ideas? Your help is MUCH appreicated!

SELECT DATE_FORMAT(date_call, '%Y') as year_name,
DATE_FORMAT(date_call, '%M') as month_name, COUNT(CASE WHEN returned = 'Y' THEN call_id ELSE NULL END) AS 'returned'
FROM calls
GROUP BY month_name, year_name

[r937]

I'd rather not build a table for all the months/years.

use a simple integers table

create table integers (i integer);
insert into integers (i) values
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);


select year_name
     , date_format(date_call,'%M') as month_name
     , count(case when returned = 'Y' 
                  then call_id else null 
              end) as 'returned'
  from (
       select distinct 
              year(date_call) as y
            , date_format(date_call,'%Y') 
                              as year_name
         from calls
       ) as years
cross 
  join integers
left outer
  join calls
    on y = year(date_call)
   and i = month(date_call)  
 where i between 1 and 12           
group 
    by year_name
     , month_name

[WebDevGuy]

Thanks! But unfortunately, I am using MySQL version 3.2x which does not support sub-queries or cross joins

[r937]

then you will need a separate table of years, which you can fake by using another copy of the integers table, provided that you specify the correct range of years

replace the words CROSS JOIN with a comma

select years.i as year_name
     , date_format(date_call,'%M') as month_name
     , count(case when returned = 'Y' 
                  then call_id else null 
              end) as 'returned'
  from integers as years
     , integers as months
left outer
  join calls
    on years.i+1999 = year(date_call)
   and months.i = month(date_call)  
 where years.i between 0 and 6 -- covers 1999-2005
   and months.i between 1 and 12           
group 
    by year_name
     , month_name