Re: Multiplying columns from different rows

If your planning on saving the data in another table then I would suggest using a trigger with the following format in pseudo code

BEFORE INSERT, UPDATE, DELTE of TABLE_NAME -- assume cust id is primary key of the summary table -- If no row was returned then we insert in this table other wise it is a update
select perc_overflow into temp1 from your_table where cust_id = ew.cust_id; -- Also assumes that the majority of the time it will exist.

if inserting then
  update table perc_overflow = per_overflow * new.perc_overflow where cust_id = new.cust_id;
  or insert statement depending if a record was found or not if updating then
  divide by the old value and the multiply by the new value   (temp1/old.perc_overflow) * new.perc_overflow if deleteing then

   update and divide by the old value
end if;

If you are looking for a query to show you this information then stick you query iin a for loop and iterate through it while checking the cust_id is the same as the last record.

HTH
Scott Watson.

<milesr_at_my-deja.com> wrote in message news:8504lv$agt$1_at_nnrp1.deja.com...
> Hello All,
>
> I'm trying to developed the code to write one cust_id per row, with
> the perc_overflow column mulitlpied by other perc_overflow columns with
> the same cust_id. I'm not sure whether I need a cursor, trigger, control
> structure, etc...
>
> This is an abbreviated report format of the database:
>
> Cust_id Location perc_overflow Phone_ext Study_Hour
> 159 FT. Bragg 42 3145 03
> 159 FT. Bragg 5 3145 03
> 159 FT. Bragg 1 3145 03
> 160 FT. Myer 8 9934 10
> 160 FT. Myer 12 9934 10
> 161 FT. Meade 14 1253 12
> 161 FT. Meade 13 1253 12
> 161 FT. Meade 16 1253 12
>
> Now, if they have the same Cust_id, I need to multiply the
> perc_overflow columns so I'd only see one record per Cust_id and the
> perc_overflow column would represent the total percentage of over flowed
> circuits for that Cust_id.
>
> The desired output would read for example:
>
> Cust_id Location perc_overflow Phone_ext Study_hour
> 159 FT. Bragg 210 3145 03
> 160 FT. Myer 96 9934 10
> 161 FT. Meade 2912 1253 12
>
> Could someone please give me some guidance towards accomplishing this
> task in PL/SQL. I'm still not getting any output, probably because none
> of my queries make any sense.
>
> Thanks,
> Ronald
>
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Jan 05 2000 - 22:17:11 CST