Thread: Help with a query, please

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?

Thanks in advance,
Timo T.

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

Dear Timo

SELECT (test1.i+test2.i) as sum, c as c
FROM test1,test2
WHERE tes1.c=test2.c

UNION

SELECT *
FROM test1,test2
WHERE ttest1.c != test2.c;

This in theory should give the answer you are looking for. Can work out
exact syntax and mail again in a couple of days.

Vijay

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?
>
> Thanks in advance,
> Timo T.