Re: How do I get the median value ?
In article <5ro4k5$jnq@cssun.mathcs.emory.edu>,
Sujit Pal <spal@scotch.den.csci.csc.com> wrote:
>
>What you seem to be looking for is the "Financial Median" which is
>calculated as follows:
>
>1. Divide the dataset into 2 halves of equal size such that all values
> in the lower half are lower than any value in the upper half.
>2. The median is the average of the highest value in the upper half and
> the lowest value in the lower half.
How about this:-
create temp table t1 ( cost money(16,2) ) with no log ;
insert into t1 values ( 10 ) ;
insert into t1 values ( 10 ) ;
insert into t1 values ( 10 ) ;
insert into t1 values ( 11 ) ;
insert into t1 values ( 12 ) ;
insert into t1 values ( 15 ) ;
insert into t1 values ( 30 ) ;
select (
( select max(cost)
from t1
where (select count(*)
from t1 t1A
where t1A.cost < t1.cost ) < (select count(*)
from t1 t1B
where t1B.cost > t1.cost )
)
+
( select min(cost)
from t1
where (select count(*)
from t1 t1A
where t1A.cost < t1.cost ) > (select count(*)
from t1 t1B
where t1B.cost > t1.cost )
)
) / 2
from systables
where tabid = 1 ?
which more or less follows your rules (1) and (2), and yields "11" as required.
Performance leaves a bit to be desired - I really wouldn't recomend this on a
90,000 element data set!
It amounts to:
- Get the highest element that has more things above it than below it;
- Get the smallest element that has more things below it than above it;
- Take the mean of the two.
Paul (not a spokesman, etc)