X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 2AC249FA612 for ; Wed, 28 Jun 2006 17:16:22 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 24938-03 for ; Wed, 28 Jun 2006 17:16:16 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.171]) by postgresql.org (Postfix) with ESMTP id 2332F9FA506 for ; Wed, 28 Jun 2006 17:16:15 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id a2so3316170ugf for ; Wed, 28 Jun 2006 13:16:14 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=Gs1sMW8aK1rsQ7wYzIy8/CkDl5xt+luvMMVQn8peo6w58hQ/X+4ILkf4uKQ298QSmxnZ5J7Pk0frtPP1Ax5VnNagEtrbUQSIaKN1tLDKekLj9aYbK1/bx3m4+8sx1M8+EGuVxhC4O5z42EnAph4vn7jcl+0cgSf7CB1fZ/77ItA= Received: by 10.78.170.17 with SMTP id s17mr732897hue; Wed, 28 Jun 2006 13:16:14 -0700 (PDT) Received: by 10.78.29.2 with HTTP; Wed, 28 Jun 2006 13:16:14 -0700 (PDT) Message-ID: Date: Wed, 28 Jun 2006 15:16:14 -0500 From: "Aaron Bono" To: "Pedro B." Subject: Re: generate_series with left join Cc: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_6609_9802966.1151525774455" References: <1151510424.28638.18.camel@m3ta.msglab.mine.nu> <1151522786.2238.46.camel@localhost.localdomain> X-Google-Sender-Auth: 45871fd9e58740c3 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/305 X-Sequence-Number: 25541 ------=_Part_6609_9802966.1151525774455 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 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. 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 > > > > ------=_Part_6609_9802966.1151525774455 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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

------=_Part_6609_9802966.1151525774455--