I recently ran into the same issue and I resolved it by generating a table of
nothing but months for the last 5 years:
select TO_CHAR((current_date - interval '1 month' * a),'YYYY-MM') AS mmyyyy
FROM generate_series(1,60,1) AS s(a)
"2010-02"
"2010-01"
"2009-12"
"2009-11"
"2009-10"
…
Then I did a join on this generated series:
SELECT months.mmyyyy
,COUNT(foo_key)
from (
select TO_CHAR((current_date - interval '1 month' *
a),'YYYY-MM') as mmyyyy
from generate_series(1,60,1) AS s(a)
) months
LEFT OUTER JOIN foo
ON
months.mmyyyy=to_char(foo_date_created,'YYYY-MM')
GROUP BY months.mmyyyy
I’m sure you can adapt this to your needs.
Garrett Murphy
From: [email protected] [mailto:[email protected]] On
Behalf Of query
Sent: Monday, March 08, 2010 5:25 AM
To: [email protected]
Subject: [SQL] list of all months
Hi,
I want to display data for all days in a month even if no data exists for that
month. Some of the days in a month might not have any data at all. With normal
query, we can display days only if data exists.But I want to display rows for
all days in a month with blank data for non-existing day in database.
How can this be achieved ?
<http://portal.mxlogic.com/redir/?atT74QSkQkjqtSkXI6zB5xAS03F8zG5ezW4DnunM5_FUTW4JPkapo-lyEH0fXkaokWfEitt4-nOAycdCT7PVg_w20ErAwwvV8TvAnXLcIELTKrKrm8Q5zOZ1oYLkN3Uw1lpp76zBc5blFcz7W2N_00jr5NPVJ5dNVNVNAsUrjodCBIo0saCBQQg7OFcQgltd46DDCy1mI9AW6V-7PM76Qjq9JeXb3bUVcQsTdxGcl-Vx8li0S>
