From: | Bob Dusek <bobd(at)palaver(dot)net> |
---|---|
To: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Aggregates and Joins |
Date: | 1998-11-24 22:13:44 |
Message-ID: | Pine.LNX.3.96.981124164406.24677D-100000@toots.palaver.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
I've kind of run into a wall, just now, and I don't know if I can climb it
with Postgres. In order to fully explain my problem, I've got to actually
go into detail about my database setup... so, if you're willing to
listen/read for a minute, I appreciate it. Just so you know, my problem -
as I see it - is related to the limitation of aggregate function use.
In my database, I have two tables. In one of the tables, I have a primary
key 'trans_id'. In the other table, I store vital information associated
with 'trans_id'. More specifically:
table first_table (
trans_id int, -- my primary key
otherfields blah
blah blah
);
table second_table (
this_id int,
price money,
...
..
trans_id int, -- my primary key association
);
In second_table, there may be any number (greater than zero) of records
associated with a single record in first_table (ie. if I have a record in
frist_table with a 'trans_id' = 33, I may have 1 or 100 records in
second_table with a 'trans_id' = 33).
What I need to do is to obtain the sum of the field 'price' from
second_table for every distinct 'trans_id' (all of which can be found in
first_table). Right now, I do not know of any way to do this, other than
by using my scripting language to first get all of the 'trans_id' values
from first_table - "select trans_id from first_table" - and then (for
each and every one of the records returned) initiating a separate query -
"select sum(price) from second_table where trans_id = onetransid" - that
would use the aggregate function sum() on the price field in second_table.
This isn't a problem if I'm only trying to get the sum(price) for a
hundred or so distinct 'trans_id' values. However, I will (more often
than not) be trying to do this for thousands of distinct 'trans_id'
values. Is there a quicker way to do this? Is it possible to use the
aggregate functions and have more than one row returned?
Here's some pseudo-code to look at, if you'd like:
// set up the query
$query = "select trans_id from first_table";
// execute the query and obtain a pointer to the results
$result = dbExec($postgresConnection, $query);
// find out how many records were returned
$numrecords = pg_NumRows($result);
// obtain the 'trans_id' values, one by one, and sum() the price
for($i = 0; $i < $numrecords; $i++) {
$trans_id = pg_Result($result, $i, 0); // record $i, index 0
$subquery = "select sum(price) from second_table " .
"where trans_id = $trans_id";
$subresult = dbExec($postgresConnection, $subquery);
// only one record - to my knowledge - can be returned
$pricesum[$trans_id] = pg_Result($result, 0, 0);
}
Any help you can give will be graciously accepted.....
Thanks,
Bob
From | Date | Subject | |
---|---|---|---|
Next Message | Oliver Elphick | 1998-11-24 23:30:19 | Re: [GENERAL] Aggregates and Joins |
Previous Message | Scot Jenkins | 1998-11-24 13:19:27 | unsubscribe |