Re: generate_series with left join

["Pedro B." <pedro(dot)borracha(at)msglab(dot)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.