## median

### median

Normal aggregate functions in Ingres/SQL include count, sum, avg, max,
and min. Has anyone tried to obtain a median value from a table
of data? I imagine one would have to use a view or something, but
has anyone done this?

Thanks
CWS

### median

: Normal aggregate functions in Ingres/SQL include count, sum, avg, max,
: and min. Has anyone tried to obtain a median value from a table
: of data? I imagine one would have to use a view or something, but
: has anyone done this?

I have tried this and in the end decided it was just too difficult
to be practical.  I don't think it is possible even in principal using
SQL alone.

For a set of N data, the median is the element indexed as (N+1)/2
(or the mean of the two middle values when N is even).  This poses
two problems: you have to number all of your data in rank order, and
you have to use a different algorithm depending on whether N is even
or odd.  It is not impossibly difficult to sort and number the data
using SQL, but it is achingly laborious to code the SQL and exceedingly
computationally intensive for even modest values of N.  Then, once you
have created a temporary table with your numbered data, you have to
decide which of the two algorithms to use, but SQL has no conditional
execution mechanism, so you have to put together a union with two
mutually exclusive WHERE clauses to fake it out.

In the end you are left with something that looks like hell, takes
forever to execute, and leaves you sweaty, angry, and exhausted.
I decided it was a waste of my energy and a bad solution, and wrote
a shell script that did the job in a trice.

--Roy Hann

### median

Chuck,

I've been out of the office for a while and I'm not sure that you got an
answer, so here's mine.  No I have not figured out a way to get the median
using ingres/sql.  What I do for basic statistics is use RDB, the reducetbl
command works well in combination with tsql to retrieve the data.

Let me know if you want some help with this, Thanks Colleen

On Wed, 25 Jan 1995 20:19:01 GMT
"Charles W. Schalk" said:

Quote:> Normal aggregate functions in Ingres/SQL include count, sum, avg, max,
> and min. Has anyone tried to obtain a median value from a table
> of data? I imagine one would have to use a view or something, but
> has anyone done this?

> Thanks
> CWS

I am trying to write a median function, but I'm very new to user
defined functions in SQL Server, so I'd appreciate any guidance anyone
can give me.

I need to write a median function so that it may be used inline in
queries, but with different criteria, as in:

SELECT MEDIAN(SELECT NumField FROM TBLB WHERE TypeOfRecord='AAA') AS
MedianValue

or maybe

SELECT MEDIAN(NumField) As MedianValue FROM TBLB WHERE
TypeOfRecord='AAA'

Can you pass a result set or something similar *TO* a user defined
function?  I can't seem to find any (productive) documentation on
this.  I have found references to RETURNING a result set, but not
relating to using a set as a parameter.

Any info is appreciated.

Thanks,
Klynt Klimek

7. Median