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

: 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

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

3. Using @@ROWCOUNT/ median function

4. Help - log reader agent error!

5. How to find Median and Mode By SQL?

7. Median

8. problem after not enough space in harddisk

9. OLAP Calculated Member - Median Function

11. Median function?

13. Median calc-member - filter out some rows

3 post • Page:**1** of **1**