Subject: Re: generate_series with left join
From: "Pedro B." <pedro.borracha@msglab.com>
To: Aaron Bono <postgresql@aranya.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <bf05e51c0606281134s57ad9b37nd1991ad7c8928dd6@mail.gmail.com>
References: <1151510424.28638.18.camel@m3ta.msglab.mine.nu>
	<bf05e51c0606281134s57ad9b37nd1991ad7c8928dd6@mail.gmail.com>
Content-Type: text/plain
Date: Wed, 28 Jun 2006 20:26:26 +0100
Message-Id: <1151522786.2238.46.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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


-- 
This message has been scanned for viruses and
dangerous content at MsgLab.com and is
believed to be clean.