"Hugo L." <hu**********@pi.be> wrote in message

news:66**************************@posting.google.c om...

I really don't know how to calculate the median. Can anybody help me?

If you want a "straight SQL" approach you could try something like this:

Let's say you have a table called dist:

create table dist

(

id counter not null primary key,

c int not null

)

The statistical median is found by:

select d1.c AS median

from dist AS d1, dist AS d2

group by d1.c

having

count(IIf(d2.c <= d1.c,1,null))=(count(*)+1)/2

The statistical median must be one of the values in the set. In this case,

if table dist has an odd number of rows, the median will be the middle

value. If the number is even, the query returns the lesser of the two middle

values.

A financial median does not have to be one of the values of the set. In the

case of an even number of values, it is the average of the two middle

values.

select IIf((count(*) Mod 2)=0,

(d1.c+min(IIf(d2.c>d1.c,d2.c,null))/2),d1.c)

as median

from dist as d1, dist as d2

group by d1.c

having

count(IIf(d2.c <= d1.c,1,null))=(count(*)+1)/2

Neither of these solutions will work properly if you have

duplicate values centered around the median. You can still handle that in

SQL but it is really complicated.