From: Aaron Bono <postgresql@aranya.com>
To: pedro.borracha@msglab.com
Cc: pgsql-sql@postgresql.org
Subject: Re: generate_series with left join
Date: Wed, 28 Jun 2006 13:34:09 -0500
Message-ID: <bf05e51c0606281134s57ad9b37nd1991ad7c8928dd6@mail.gmail.com> (raw)
In-Reply-To: <1151510424.28638.18.camel@m3ta.msglab.mine.nu>
References: <1151510424.28638.18.camel@m3ta.msglab.mine.nu>
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
>
>
>
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: <bf05e51c0606281134s57ad9b37nd1991ad7c8928dd6@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