Re: Multiplying columns from different rows

If perc_overflow is guaranteed to be positive, then something like this will work:

SELECT Cust_id, MAX(Location), EXP(SUM(LN(perc_overflow))),

        MAX(Phone_ext),MAX(Study_Hour)
FROM MyTable
GROUP BY Cust_id

If some of the values can be negative, then you can modify this by counting the number of negative values and checking whether this number is even or odd using DECODE. There was also a portable solution to your problem published in the RDBMS Magazine two or three years ago (look up the Joe Celcko's "SQL For Smarties" column), but I do not have it with me.

Of course, you can always get the job done by looping through a cursor selecting from your table with an ORDER BY Cust_id clause, but tough guys don't use cursors unless forced to do so :-)

On Wed, 05 Jan 2000 19:05:47 GMT, milesr_at_my-deja.com wrote:

> 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.
>
Received on Thu Jan 06 2000 - 10:54:54 CST