Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2008
    Posts
    11

    Question Unanswered: SQL-how to get missing months

    Hi,
    i am using a sql like this :

    SELECT count(*) AS count_all, DATE_FORMAT(created_at, '%b %Y') AS date_format FROM users join user_general_info ON users.id = user_general_info.user_id WHERE (created_at BETWEEN '2008-03-01' and '2008-09-04') GROUP BY DATE_FORMAT(created_at, '%b %Y') ORDER BY created_at

    and getting output like this :

    count_all date_format
    2 Mar 2008
    1 Apr 2008
    4 Jun 2008
    10 Jul 2008
    12 Aug 2008

    i want to get the missing months data in tables from jan 2008 to Sep 2008 .But i was getting only records that were present in the db tables

    but i would like the output to be :
    count_all date_format
    0 Jan 2008
    0 Feb 2008
    2 Mar 2008
    1 Apr 2008
    0 May 2008
    4 Jun 2008
    10 Jul 2008
    12 Aug 2008

    can we get zeros or nil values for missing months queried between jan and sept ?

    thanks,
    Srinath

  2. #2
    Join Date
    Sep 2008
    Posts
    11

    SQL-how to get missing months

    Hi,
    i am using a sql like this :

    SELECT count(*) AS count_all, DATE_FORMAT(created_at, '%b %Y') AS date_format FROM users join user_general_info ON users.id = user_general_info.user_id WHERE (created_at BETWEEN '2008-03-01' and '2008-09-04') GROUP BY DATE_FORMAT(created_at, '%b %Y') ORDER BY created_at

    and getting output like this :

    count_all date_format
    2 Mar 2008
    1 Apr 2008
    4 Jun 2008
    10 Jul 2008
    12 Aug 2008

    i want to get the missing months data in tables from jan 2008 to Sep 2008 .But i was getting only records that were present in the db tables

    but i would like the output to be :
    count_all date_format
    0 Jan 2008
    0 Feb 2008
    2 Mar 2008
    1 Apr 2008
    0 May 2008
    4 Jun 2008
    10 Jul 2008
    12 Aug 2008

    can we get zeros or nil values for missing months queried between jan and sept ?

    thanks,
    Srinath

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    you need to create a "calendar" table
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here's an example; I have created a table (its name is "test") which contains result of your current query (as you didn't provide your test case):
    Code:
    SQL> SELECT cnt, TO_CHAR(date_format, 'mon yyyy') df
      2  FROM test
      3  ORDER BY date_format;
    
           CNT DF
    ---------- --------
             2 mar 2008
             1 apr 2008
             4 jun 2008
            10 jul 2008
            12 aug 2008
    As you'd like to complete months list, you could use UNION operator which would unite your result with a generated current year's calendar. LEVEL pseudocolumn is used to create this calendar; NOT IN will have to rely on your data (I used my "test" table).
    Code:
    SQL> SELECT y.cnt, TO_CHAR(y.date_format, 'mon yyyy') df
      2  FROM (SELECT t.cnt, t.date_format
      3        FROM TEST t
      4        UNION
      5        SELECT 0 cnt, x.datum
      6        FROM (SELECT ADD_MONTHS(TRUNC(SYSDATE, 'yyyy'), LEVEL - 1) datum
      7              FROM dual
      8              CONNECT BY LEVEL <= 12
      9             ) x
     10        WHERE datum NOT IN (SELECT date_format FROM TEST)
     11          AND datum <= (SELECT MAX(date_format) FROM TEST)
     12       ) y
     13  ORDER BY y.date_format;
    
           CNT DF
    ---------- --------
             0 jan 2008
             0 feb 2008
             2 mar 2008
             1 apr 2008
             0 may 2008
             4 jun 2008
            10 jul 2008
            12 aug 2008
    
    8 rows selected.
    
    SQL>

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Try a LEFT JOIN to a months/calendar table.
    George
    Home | Blog

  6. #6
    Join Date
    Sep 2008
    Posts
    11
    hi georgev,
    i was not using any months/calendar table as i was formating in to months name using date_format on my created_at column where it is type Date

    thanks !

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Similar to Littlefoot's query, if you have to span a year boundary you could outer join your table to this subquery

    Code:
    select add_months(to_date(200801,'yyyymm'), rn-1)
    from
    (
    	SELECT ROWNUM AS rn 
    	FROM   dual
    	CONNECT BY ROWNUM <= (select months_between(to_date(200808,'yyyymm') , to_date(200801,'yyyymm'))+1 from dual)
    )
    Alan

  8. #8
    Join Date
    Sep 2008
    Posts
    11
    Hi,
    i was not using any months/calendar table as i was formating in to months name using date_format on my created_at column where it is type Date.

    SQL :
    SELECT count(*) AS count_all, DATE_FORMAT(created_at, '%b %Y') AS date_format FROM users join user_general_info ON users.id = user_general_info.user_id WHERE (created_at BETWEEN '2008-03-01' and '2008-09-04') GROUP BY DATE_FORMAT(created_at, '%b %Y') ORDER BY created_at

    i am retreiving records by using only one SQL ,
    joining tables users and user_general_info where created_at exists in users table.

    thanks for your posts.
    Last edited by srinath.a; 09-04-08 at 13:27.

Posting Permissions

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