Thread: Aggregates and Joins

Aggregates and Joins

From
Bob Dusek
Date:
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


Re: [GENERAL] Aggregates and Joins

From
"Oliver Elphick"
Date:
Bob Dusek wrote:
...
  >table second_table (
  >
  >   this_id int,
  >   price money,
  >   ...
  >   ..
  >   trans_id int, -- my primary key association
  >
  >);
...
  >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). ... Is there a quicker way to do this?  Is it possible to
  >use the aggregate functions and have more than one row returned?

select sum(price) from second_table group by trans_id;

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
               PGP key from public servers; key ID 32B8FAA1
                 ========================================
     "Jesus saith unto him, I am the way, the truth, and the
      life; no man cometh unto the Father, but by me."
                                         John 14:6



Re: [GENERAL] Aggregates and Joins

From
Bob Dusek
Date:
Hey!!!

Thanks a lot.  That is just what I needed.

here's the query I used:

select sum(price), trans_id from second_table group by trans_id;

I had to include the "trans_id" in the target list to use it for grouping.
I've never been able to mix single fields and aggregates before (of
course, I've never used the "group" syntax before either).

Thanks a lot.

Bob


On Tue, 24 Nov 1998, Oliver Elphick wrote:

> Date: Tue, 24 Nov 1998 23:30:19 +0000
> From: Oliver Elphick <olly@lfix.co.uk>
> To: Bob Dusek <bobd@palaver.net>
> Cc: PostgreSQL General <pgsql-general@postgreSQL.org>
> Subject: Re: [GENERAL] Aggregates and Joins
>
> Bob Dusek wrote:
> ...
>   >table second_table (
>   >
>   >   this_id int,
>   >   price money,
>   >   ...
>   >   ..
>   >   trans_id int, -- my primary key association
>   >
>   >);
> ...
>   >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). ... Is there a quicker way to do this?  Is it possible to
>   >use the aggregate functions and have more than one row returned?
>
> select sum(price) from second_table group by trans_id;
>
> --
> Oliver Elphick                                Oliver.Elphick@lfix.co.uk
> Isle of Wight                              http://www.lfix.co.uk/oliver
>                PGP key from public servers; key ID 32B8FAA1
>                  ========================================
>      "Jesus saith unto him, I am the way, the truth, and the
>       life; no man cometh unto the Father, but by me."
>                                          John 14:6
>
>
>


Re: [GENERAL] Aggregates and Joins

From
"PAX!"
Date:
Hello

If I have well understood what you want, you can do it by using a "group
by" clause. In the case you proposed, it should be used like:

select ref, sum(price)
from second_table
group by trans_id ;

I've tried this and it works for my database. Hope it helps


Bob Dusek wrote:
>
> 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

--
La risa es la actitud primigenia hacia la vida:
un modo de acercamiento que pervive sólo en
criminales y artistas
            -- Oscar Wilde