DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:from:date:message-id:subject:to
	:cc:content-type:content-transfer-encoding;
	b=ACLgccN0vCTflIB3asuiC+Py2j9bhoBUmwjI/2qEFG74gA+j5YPMRN4/1UOyV2cQ7t
	74NDnD/PVaqMN3326D4cxcEbSbXURcsbUc4NzHJx5aIKBYDDWAADk3JbrosLfylx9tzQ
	tDmegU1SzxwcU4aVLZO3HYLHQozMQS21B5cH4=
MIME-Version: 1.0
In-Reply-To: <20100308122523.50565.qmail@f5mail-237-209.rediffmail.com>
References: <20100308122523.50565.qmail@f5mail-237-209.rediffmail.com>
From: Dawid Kuroczko <qnex42@gmail.com>
Date: Mon, 15 Mar 2010 23:25:22 +0100
Message-ID: <758d5e7f1003151525w529f7a49ha55a6e693100abc6@mail.gmail.com>
Subject: Re: list of all months
To: query <search2006@rediffmail.com>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Mon, Mar 8, 2010 at 13:25, query <search2006@rediffmail.com> wrote:
>
> 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. Wi=
th normal query, we can display days only if data exists.But I want to disp=
lay rows for all days in a month with blank data for non-existing day in da=
tabase.
>
> How can this be achieved ?

Say, you have a table like:

CREATE TABLE some_data (
   date date NOT NULL,
   some_value int
);

Now, You would like to print values
from March 2010, even if there is no
entry for some days in such a table.

We need to have a list of all the days
in March.  We can do it with a query:

SELECT date '2010-03-01' + n AS date
  FROM generate_series(0, date '2010-04-01' - date'2010-03-01' - 1) AS x(n)=
;

Sweet, we have dates, we just need a LEFT JOIN now:

SELECT date,
       coalesce(value, 0) AS value
   FROM some_data
   RIGHT JOIN (
     SELECT date '2010-03-01' + n AS date
       FROM generate_series(0, date '2010-04-01' - date'2010-03-01' - 1) AS=
 x(n)
     ) AS dates USING (date);

If you are running fairy recent PostgreSQL
it could be written even nicer:

WITH dates AS (
  SELECT date '2010-03-01' + n AS date
       FROM generate_series(0, date '2010-04-01' - date'2010-03-01' - 1) AS=
 x(n)
)
SELECT date,
       coalesce(value, 0) AS value
      FROM dates
     LEFT JOIN some_data USING (date);


Two remarks:
 - it is fairy easy to create generate_series(date, date) function.
   Give it a try - its fun! :)
 - coalesce function will provide 0 in places where there is no
   data row, or value is NULL.

Best regards,
    Dawid