Re: generate_series with left join

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(dot)borracha(at)msglab(dot)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
>
>