From: Pedro B. <pedro.borracha@msglab.com>
To: Aaron Bono <postgresql@aranya.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: generate_series with left join
Date: Wed, 28 Jun 2006 21:28:30 +0100
Message-ID: <1151526511.2238.52.camel@localhost.localdomain> (raw)
In-Reply-To: <bf05e51c0606281316n4a90cd50m51fa89922a878856@mail.gmail.com>
References: <1151510424.28638.18.camel@m3ta.msglab.mine.nu>
<bf05e51c0606281134s57ad9b37nd1991ad7c8928dd6@mail.gmail.com>
<1151522786.2238.46.camel@localhost.localdomain>
<bf05e51c0606281310l5db30741g97678d71c837266d@mail.gmail.com>
<bf05e51c0606281316n4a90cd50m51fa89922a878856@mail.gmail.com>
On Wed, 2006-06-28 at 15:16 -0500, Aaron Bono wrote:
> This should work too:
>
> select
> year_list.year,
> one_list.one_count,
> two_list.two_count
> FROM (
> select years
> from generate_series(2006,2009) as years
> ) year_list
> left outer join (
> select
> date_part('year', one.date) as one_year,
> count(one.*) as one_count
> from mytable as one
> where one.cause = 1
> group by
> date_part('year', one.date)
> ) one_list on (year_list.years = one_year)
> left outer join (
> select
> date_part('year', two.date) as two_year,
> count(two.*) as two_count
> from mytable as two
> where two.cause = 2
> group by
> date_part('year', two.date)
> ) two_list on (year_list.years = two_year)
> ;
Aaron,
I confess i will take some time to digest the amazing code you just
sent, but in the meantime, let me tell you right away that both work
just as i needed.
I will stop pulling my hairs now.
Thank you so much.
\\pb
--
This message has been scanned for viruses and
dangerous content at MsgLab.com and is
believed to be clean.
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: pedro.borracha@msglab.com, postgresql@aranya.com
Subject: Re: generate_series with left join
In-Reply-To: <1151526511.2238.52.camel@localhost.localdomain>
* 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