Re: generate_series with left join - Mailing list pgsql-sql
| From | Aaron Bono |
|---|---|
| Subject | Re: generate_series with left join |
| Date | |
| Msg-id | bf05e51c0606281316n4a90cd50m51fa89922a878856@mail.gmail.com Whole thread Raw |
| In response to | Re: generate_series with left join ("Aaron Bono" <postgresql@aranya.com>) |
| Responses |
Re: generate_series with left join
|
| List | pgsql-sql |
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)
;
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_listleft 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