Re: [GENERAL] Query I cannot work out - Mailing list pgsql-general

Hmmm...its easier to read when you place the key'd column first and the data
second :)

If I understand you right:
t1 = detail table
t2 = master (or summary or cumulative or historical) table
var1 = data to be sum'd
var2 = key value

If the table is being used to summarize data on a daily/weekly basis to make
it faster for users to access the numbers you might want to try saving each
sum with a timestamp for that day and adding all the ones that were done in
a week when ever you want to display totals for a week.  This allows you
reprocess the total if for some reason processing fails.  Same thing applies
if it is a summary of data so that you can archive the existing detail data
and forget about it...(and more importantly you dont have to add the sum'd
value to an existing historical value :)

Sorry I don't have the time to actually sit down and work out the exact SQL,
bur here's a genaral description...
To do the query using your requirements, you will have to do it in multiple
steps cause you cant combine an aggregate function like SUM with non
aggregate calculations (I think).  So first do a Insert/select to a temp
table summing the detail values and then update/select to add them to the
cumulative value and write out.

-----Original Message-----
From: M Simms <grim@argh.demon.co.uk>
To: pgsql-general@postgreSQL.org <pgsql-general@postgreSQL.org>
Date: Friday, April 30, 1999 10:29 PM
Subject: [GENERAL] Query I cannot work out


>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
>
>