I read the follow query about calculating median posted by Daivd Porta

on 10/8/03.

CREATE TABLE SomeValues (keyx CHAR(1) PRIMARY KEY, valuex INTEGER NOT

NULL)

INSERT INTO SomeValues VALUES ('A',1)

INSERT INTO SomeValues VALUES ('B',2)

INSERT INTO SomeValues VALUES ('C',3)

INSERT INTO SomeValues VALUES ('D',4)

INSERT INTO SomeValues VALUES ('E',5)

SELECT S1.valuex AS median

FROM SomeValues AS S1, SomeValues AS S2

GROUP BY S1.valuex

HAVING SUM(CASE WHEN S2.valuex <= S1.valuex

THEN 1 ELSE 0 END)

= ((COUNT(*) + 1) / 2)
AND SUM(CASE WHEN S2.valuex >= S1.valuex

THEN 1 ELSE 0 END)= (COUNT(*)/2 + 1)

I have difficulty to understand the having clause. If S1 and S2 are

the same table, what it means by S2.valuex >= S1.valuex? Could some

give me a help?

Also, if I have a table structured as:

classID field1 field2 field3

c1 1 2 3

c1 4 5 6

c1 7 8 9

c2 9 8 7

c2 6 5 4

c2 3 2 1

Is there a way to create a user-defined function that can get the

median for each field as easy as the average function. Such as

select distinct classID,

median(field1),

median(field2),

median(field3)

from [tablename]

group by classID

Thanks in advance