Re: Help with a query, please

Timo Tuomi wrote:
>
>testdb=>
>testdb=> select * from test1;
>
>c|i
>-+-
>A|2
>B|3
>C|4
>(3 rows)
>
>testdb=> select * from test2;
>
>c|i
>-+-
>A|6
>C|7
>(2 rows)
>
>testdb=>
>
>I'd like to get (test1.i + test2.i) like this:
>
>c|sum
>-+---
>A|8
>B|3
>C|11
>
>
>i.e. to sum the rows but if one of the rows doesn't exist on one of the
>tables then print just the existing row. How can I do this?

junk=# select coalesce(test1.c,test2.c) as c,(coalesce(test1.i,0) +
coalesce(test2.i,0)) as sum from test1 full outer join test2 on test1.c =
test2.c;
c | sum
---+-----
A | 8
B | 3
C | 11
(3 rows)

I think you need to install PostgreSQL 7.1 to do this.

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Lay not up for yourselves treasures upon earth, where
moth and rust doth corrupt, and where thieves break
through and steal; But lay up for yourselves treasures
in heaven, where neither moth nor rust doth corrupt,
and where thieves do not break through nor steal; For
where your treasure is, there will your heart be
also." Matthew 6:19-21