From: Dawid Kuroczko <qnex42@gmail.com>
To: query <search2006@rediffmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: list of all months
Date: Mon, 15 Mar 2010 23:25:22 +0100
Message-ID: <758d5e7f1003151525w529f7a49ha55a6e693100abc6@mail.gmail.com> (raw)
In-Reply-To: <20100308122523.50565.qmail@f5mail-237-209.rediffmail.com>
References: <20100308122523.50565.qmail@f5mail-237-209.rediffmail.com>
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. 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 ?
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: qnex42@gmail.com, search2006@rediffmail.com
Subject: Re: list of all months
In-Reply-To: <758d5e7f1003151525w529f7a49ha55a6e693100abc6@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox