Query I cannot work out

From: M Simms <grim(at)argh(dot)demon(dot)co(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Query I cannot work out
Date: 1999-05-01 02:14:29
Message-ID: 199905010214.DAA26007@argh.demon.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I cannot work out how to do the following query

testdb=> select * from t1;
var1|var2
----+----
1| 2
2| 2
2| 3
(3 rows)

testdb=> select * from t2;
var1|var2
----+----
5| 2
9| 3
4| 4
(3 rows)

I need to add to the value of each var1 in t2 the sum of all var1's in t1
that have the same value corresponding var2's

testdb=> update t2 set var1=t2.var1+sum(t1.var1) where t1.var2=t2.var2;
ERROR: parser: illegal use of aggregates or non-group column in target list

I imagine that this is failing because it isnt sure which values to
sum(), but I cannot for the life of me figure out how to solve this.

The values I need to end up with, just so you know you are solving the
right problem, are:

testdb=> select * from t1;
var1|var2
----+----
1| 2
2| 2
2| 3
(3 rows)

testdb=> select * from t2;
var1|var2
----+----
8| 2
11| 3
4| 4
(3 rows)

Any help on this would be appreciated. I am a bit stuck here, and I am
sure I am just missing something blindingly obvious.
I am loathe to use a cursor to do this, because my actual dataset is
upwards of 10,000 records, and I do not wish to do 10,000 different
queries.

On a second note, I am having a problem with the database itself...

I keep getting:

NOTICE: SIMarkEntryData: cache state reset

And after a while, the database stops responding. This is under
moderately light use, maybee 25 open connections, and most of them
idle 90% of the time.

Now, this is on a 6.3 database, and I dont have access to my 6.4 dbase
right now, is this some problem that will go away on the newer
version? Possibly because of the limit of open descriptors to the
database?

Thanks

M Simms

Browse pgsql-general by date

  From Date Subject
Next Message Ulf Mehlig 1999-05-01 11:16:11 cluster truncates table name?
Previous Message Michael Konrad 1999-04-30 18:23:58 RedHat 5.2 & PostgreSQL 6.3.2