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 D60689FA425 for ; Wed, 28 Jun 2006 15:34:21 -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 09314-07 for ; Wed, 28 Jun 2006 15:34:11 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.172]) by postgresql.org (Postfix) with ESMTP id 0EE339FA319 for ; Wed, 28 Jun 2006 15:34:10 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id a2so3263573ugf for ; Wed, 28 Jun 2006 11:34:09 -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=SHTuoukDnYiY+Me25NMuMFcLKSHx8pcps3dy6EhEun5Hu5Wa0QlS4GYLkayxzT8kEl6dRqF6WBL4po2yERveXfSJrjwgri6Zg8kWT0/Nc6iTbErdoau+dFvximEypYzQl2xHrspIQwdUH2gMuTeK9eT6q5N9zgSSx9uTR1+Sqsw= Received: by 10.78.185.7 with SMTP id i7mr680178huf; Wed, 28 Jun 2006 11:34:09 -0700 (PDT) Received: by 10.78.29.2 with HTTP; Wed, 28 Jun 2006 11:34:09 -0700 (PDT) Message-ID: Date: Wed, 28 Jun 2006 13:34:09 -0500 From: "Aaron Bono" To: pedro.borracha@msglab.com Subject: Re: generate_series with left join Cc: pgsql-sql@postgresql.org In-Reply-To: <1151510424.28638.18.camel@m3ta.msglab.mine.nu> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_4862_9958143.1151519649429" References: <1151510424.28638.18.camel@m3ta.msglab.mine.nu> X-Google-Sender-Auth: cd4035c039951b31 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/302 X-Sequence-Number: 25538 ------=_Part_4862_9958143.1151519649429 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline How about one of these two: 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 ; 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. -Aaron Bono On 6/28/06, Pedro B. wrote: > > Greetings. > > I'm having some difficulties with my first use of the generate_series > function. > > Situation: > cause | integer > date | timestamp(2) without time zone > > cause | date > ------+------------+-----------+ > 1 | 2006-03-23 15:07:53.63 | > 2 | 2006-02-02 12:13:23.11 | > 2 | 2006-11-12 16:43:11.45 | > 1 | 2005-03-13 18:34:44.13 | > 3 | 2006-01-23 11:24:41.31 | > (etc) > > What i need to do, is to count the 'cause' column for the values '1' and > '2', and group them by year, using left joins in order to also have the > serialized years with empty values in the output. > > My needed output for a series of (2005,2007) would be: > year | one | two > ------+------+------ > 2005 | 1 | 0 > 2006 | 1 | 2 > 2007 | 0 | 0 > > > I have tried something like > > #select s, (select count(cause) from mytable where cause=1 ) as one, > COUNT (cause) as two from generate_series(2006,2009) AS s(d) left JOIN > mytable o ON (substr(o.date,1,4) = s.d and cause=2) GROUP BY s.d ORDER > BY 1; > > which obviously is wrong, because of the results: > s | one | two > ------+------+------ > 2006 | 3769 | 1658 > 2007 | 3769 | 0 > 2008 | 3769 | 0 > 2009 | 3769 | 0 > > As far as the 'two', the left join was successful, however i can not > find a way to join the 'one'. The output value is correct, but the > result shown should be only for the year 2006, not for all the values of > the series. > Maybe i've looked at it TOO much or maybe i'm completely failing to find > a working logic. > Any suggestions? > > Any and all help is humbly appreciated. > > \\pb > > > ------=_Part_4862_9958143.1151519649429 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline How about one of these two:

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
;

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.

-Aaron Bono

On 6/28/06, Pedro B. <pedro.borracha@msglab.com> wrote:
Greetings.

I'm having some difficulties with my first use of the generate_series
function.

Situation:
cause                | integer
date                 | timestamp(2) without time zone

cause | date
------+------------+-----------+
1     | 2006-03-23 15:07:53.63 |
2     | 2006-02-02 12:13:23.11 |
2     | 2006-11-12 16:43:11.45 |
1     | 2005-03-13 18:34:44.13 |
3     | 2006-01-23 11:24:41.31 |
(etc)

What i need to do, is to count the 'cause' column for the values '1' and
'2', and group them by year, using left joins in order to also have the
serialized years with empty values in the output.

My needed output for a series of (2005,2007) would be:
year | one  | two
------+------+------
2005 |    1 |    0
2006 |    1 |    2
2007 |    0 |    0


I have tried something like

#select s, (select count(cause) from mytable where cause=1 ) as one,
COUNT (cause) as two from generate_series(2006,2009) AS s(d) left JOIN
mytable o ON (substr(o.date,1,4) = s.d and cause=2) GROUP BY s.d ORDER
BY 1;

which obviously is wrong, because of the results:
  s   | one  | two
------+------+------
2006 | 3769 | 1658
2007 | 3769 |    0
2008 | 3769 |    0
2009 | 3769 |    0

As far as the 'two', the left join was successful, however i can not
find a way to join the 'one'. The output value is correct, but the
result shown should be only for the year 2006, not for all the values of
the series.
Maybe i've looked at it TOO much or maybe i'm completely failing to find
a working logic.
Any suggestions?

Any and all help is humbly appreciated.

\\pb



------=_Part_4862_9958143.1151519649429--