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.Elphick@lfix.co.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