Re: Median function in SQL

Dave McRae <dmcrae_at_pcug.org.au> wrote in article <6dimf6$uba$2_at_bomb.dynamite.com.au>...
> select revenue, max(cntr) from
> (select revenue, count(*) as cntr
> from company_financials
> where ....
> group by revenue)

that, I think, is the mode, not the median

-- 


> Kevin Merritt wrote in message

> <01bd4482$5787d980$c1015c91_at_KMERRITT.GENEVACO.COM>...

> >Does anyone have any idea how to implement a MEDIAN function in Oracle?

 For


> >example, I would like to perform the following SQL SELECT statement:

> >

> > SELECT MEDIAN(revenue) FROM company_financials WHERE revenue > 1000000

> >

> >I can write a stored procedure for each table/column I want to perform

 this


> >for, but I would like to somehow extend the built-in environment to have

> >the ultimate flexiblity.


I don't believe that you can build and use a group function like that. One
way to go, excuse the sloppy, non-generic coding, is:

function f$median
        cursor c1 is select revenue from company_financials order by
revenue;
        type tn is table of number index by binary_integer;
        t1 tn;
        median number;
begin
        for r1 in c1
        loop
                t1(c1%rowcount) := r1.c1;
        end loop;
        if mod(t1.last, 2) = 0
        then
                median := (t1(t1.last/2) + t1((t1.last/2) + 1)) / 2;
        elsif mod(t1.last, 2) = 1
        then
                median := t1(ceil(t1.last/2));
        end if;
        return median;
end;
/

to make it more generic, pass in either table, column name and where
clause, or whole sql statement, as parameters and use dbms_sql

Andrew - Wizzard

barnetaj_at_bp.com