SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WebDevGuy
    I'd rather not build a table for all the months/years.
    use a simple integers table
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks! But unfortunately, I am using MySQL version 3.2x which does not support sub-queries or cross joins

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •