"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.
