Re: Calculate median with ORACLE??

From: Chrysalis <cellis_at_iol.ie>
Date: 1997/02/19
Message-ID: <330B2781.78B6@iol.ie>#1/1

Sylvain Grenier wrote:
> values. I have to calculate minimum, maximum, average and median.

This cannot be done with any combination of standard group functions i a single SQL statement since the value required is a single-row value (or possibly the average of two rows).

Nor can it be done with a user-defined function in a single SQL statement (which operates on every row).

The simplest way is to write a procedure to do the following:

a) Count the rows containing non-null values of the target column
b) Re-read the table ORDERED BY TARGET COLUMN
c) If N is odd the value you want is in row (N+1)/2. If N is even you

want the mean of the values in rows N/2 and N/2+1

If you are interested, I have a generalised PL/SQL procedure (NOT function!) which uses embedded dynamic SQL and has arguments (table_name IN varchar2, column_name IN varchar2, median OUT number) Received on Wed Feb 19 1997 - 00:00:00 CST