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
This should work too:select year_list.year, one_list.one_count, two_list.two_countFROM ( select years from generate_series(2006,2009) as years) year_listleft outer join (
select date_part('year', one.date) as one_year, count(one.*) as one_count from mytable as on
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_countFROM( select year_list.year, count(one.*) as
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.y
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_listleft outer join mytable as one on (
date_part('year', one.date) = year_list.years and one.cause = 1)left outer join mytable as two
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
