Subject: generate_series with left join
From: "Pedro B." <pedro.borracha@msglab.com>
Reply-To: pedro.borracha@msglab.com
To: pgsql-sql@postgresql.org
Content-Type: text/plain
Organization: MsgLab AS
Date: Wed, 28 Jun 2006 17:00:24 +0100
Message-Id: <1151510424.28638.18.camel@m3ta.msglab.mine.nu>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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     | 2006-02-02 12:13:23.11 |
2     | 2006-11-12 16:43:11.45 |
1     | 2005-03-13 18:34:44.13 |
3     | 2006-01-23 11:24:41.31 |
(etc)

What i need to do, is to count the 'cause' column for the values '1' and
'2', and group them by year, using left joins in order to also have the
serialized years with empty values in the output.

My needed output for a series of (2005,2007) would be:
 year | one  | two
------+------+------
 2005 |    1 |    0
 2006 |    1 |    2
 2007 |    0 |    0


I have tried something like

#select s, (select count(cause) from mytable where cause=1 ) as one,
COUNT (cause) as two from generate_series(2006,2009) AS s(d) left JOIN
mytable o ON (substr(o.date,1,4) = s.d and cause=2) GROUP BY s.d ORDER
BY 1;

which obviously is wrong, because of the results:
  s   | one  | two
------+------+------
 2006 | 3769 | 1658
 2007 | 3769 |    0
 2008 | 3769 |    0
 2009 | 3769 |    0

As far as the 'two', the left join was successful, however i can not
find a way to join the 'one'. The output value is correct, but the
result shown should be only for the year 2006, not for all the values of
the series.
Maybe i've looked at it TOO much or maybe i'm completely failing to find
a working logic. 
Any suggestions?

Any and all help is humbly appreciated.

\\pb


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