Re: Help: Oracle MEAN Function

Eisen Chao wrote:
>
> Howdy All:
>
> My problem is as follows:
>
> If
>
> table RECORD (
> office varchar2(3),
> type varchar2(3),
> type_amt number(9)
> )
>
> Where
>
> field 'type' is either 'ASP', 'LBR', 'MAT'
> and has a value ('type_amt') associated
> with it.
>
> How would I find
>
> The MEAN for
>
> each office
> each type
>
> if MEAN is defined as
>
> ( MAX(type_amt) + MIN(type_amt) ) / 2
>
> I looked thru the manual and there are only STDDEV, AVG,
> and a few other statistical functions.
>
> Do I have to write my own PL/SQL function to do this or
> has something similar been written already that I could
> study ?
>
> I have 2,000,000 records, 200+ offices.
>
> How do I approach this so my code isn't sluggish
> or a resource hog ? I dread the thought of
> creating a PL/SQL array of 200 'offices'.
>
> Does Oracle have a set of 'magic' statistical function bullets
> somewhere ?
>
> Thanks in Advance,
>
> Eisen

select office, type, (max(amt)+min(amt))/2 from table
group by office, type

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse