Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2008
    Posts
    3

    Unanswered: SQL question, missing days

    I have a table that has data stored by the day of the year. I want to get data for all the days in a particular month, but some days are missing because we only store data for a day when we have it. Is there a query I can use to return data for all days of the month and 0 if a day is missing? Thx for the help

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    You can do this by using generate_series to generate all the dates you need and then OUTER JOIN on dates with your real table :

    Code:
    select dates.d, 
        count(l.*)
    from postgres_log l right outer join 
        (select current_date - s.a as d from generate_series(1, 30) s(a)) dates
    	on (dates.d = date_trunc('day', log_time))
    group by dates.d
    order by dates.d;
    gives :

    Code:
    "2008-06-15";0
    "2008-06-16";25
    "2008-06-17";102
    "2008-06-18";251
    "2008-06-19";241
    "2008-06-20";144
    "2008-06-21";0
    "2008-06-22";0
    "2008-06-23";0
    "2008-06-24";83
    "2008-06-25";94
    "2008-06-26";74
    "2008-06-27";62
    "2008-06-28";0
    "2008-06-29";0
    "2008-06-30";114
    "2008-07-01";108
    "2008-07-02";0
    "2008-07-03";0
    "2008-07-04";0
    "2008-07-05";0
    "2008-07-06";0
    "2008-07-07";0
    "2008-07-08";158
    "2008-07-09";139
    "2008-07-10";145
    "2008-07-11";135
    "2008-07-12";0
    "2008-07-13";0
    "2008-07-14";0
    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  3. #3
    Join Date
    Apr 2008
    Posts
    3

    thx

    Thank you so much for the help.

Posting Permissions

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