From: Aaron Bono <postgresql@aranya.com>
To: Pedro B. <pedro.borracha@msglab.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: generate_series with left join
Date: Wed, 28 Jun 2006 15:16:14 -0500
Message-ID: <bf05e51c0606281316n4a90cd50m51fa89922a878856@mail.gmail.com> (raw)
In-Reply-To: <bf05e51c0606281310l5db30741g97678d71c837266d@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>
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)
;
On 6/28/06, Aaron Bono <postgresql@aranya.com> wrote:
>
> Sorry, I think I see the mistake - it is getting all the rows for 1 and
> all the rows for 2 and joining them. Try splitting up the query into two
> inner queries like so:
>
> select
> one_list.year,
> one_list.one_count,
> two_list.two_count
> FROM
> (
> select
> year_list.year,
> count(one.*) as one_count
>
> from (
> select years
> from generate_series(2006,2009) as years
> ) year_list
> left outer join mytable as one on (
> date_part('year', one.date) = year_list.years
> and one.cause = 1
> )
> group by
> year_list.year
> ) one_list,
> (
> select
> year_list.year,
> count(two.*) as two_count
>
> from (
> select years
> from generate_series(2006,2009) as years
> ) year_list
> left outer join mytable as two on (
> date_part('year', two.date ) = year_list.years
> and two.cause = 2
> )
> group by
> year_list.year
> ) two_list
> WHERE one_list.year = two_list.year
> ;
>
> On 6/28/06, Pedro B. <pedro.borracha@msglab.com> wrote:
>
> > On Wed, 2006-06-28 at 13:34 -0500, Aaron Bono wrote:
> >
> > > select
> > > year_list.year,
> > > count(one.*),
> > > count(two.*)
> > > from (
> > > select years
> > > from generate_series(2006,2009) as years
> > > ) year_list
> > > left outer join mytable as one on (
> > > date_part('year', one.date) = year_list.years
> > > and one.cause = 1
> > > )
> > > left outer join mytable as two on (
> > > date_part('year', two.date) = year_list.years
> > > and two.cause = 2
> > > )
> > > group by
> > > year_list.year
> > > ;
> > >
> > >
> > > select
> > > year_list.year,
> > > mytable.cause,
> > > count(mytable.*)
> > > from (
> > > select years
> > > from generate_series(2006,2009) as years
> > > ) year_list
> > > left outer join mytable on (
> > > date_part('year', mytable.date) = year_list.years
> > > )
> > > group by
> > > year_list.year,
> > > mytable.cause
> > > ;
> > >
> > Aaron,
> > Thank you so much for your reply.
> > However, the 2 examples you provided have "weird" outputs:
> >
> > The first:
> > years | count | count
> > -------+---------+---------
> > 2009 | 0 | 0
> > 2008 | 0 | 0
> > 2007 | 0 | 0
> > 2006 | 7802080 | 7802080
> > (4 rows)
> >
> > Time: 87110.753 ms << yay.
> >
> >
> > The second:
> >
> > years | cause | count
> > -------+---------+-------
> > 2009 | | 0
> > 2008 | | 0
> > 2007 | | 0
> > 2006 | 6 | 1
> > 2006 | 1 | 4030
> > 2006 | 2 | 1936
> > 2006 | 3 | 4078
> > 2006 | 100 | 3159
> > 2006 | 98 | 2659
> > 2006 | 99 | 2549
> >
> > My need is really to only group the counts of where cause=1 and cause=2
> > for each year, none of the others.
> >
> > > I think one of the problems many people have is the writing of their
> > > SQL in paragraph form. It makes the SQL really hard to read and even
> > > harder to understand and debug. Formatting your SQL like I did above
> > > may make it easier to see what is wrong.
> >
> > Indeed. Note taken, i'll improve my formatting.
> >
> > \\pb
> >
> >
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: postgresql@aranya.com, pedro.borracha@msglab.com
Subject: Re: generate_series with left join
In-Reply-To: <bf05e51c0606281316n4a90cd50m51fa89922a878856@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