Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
'MEDIAN' 1
- Thread starter nibeck
- Start date
-
1
- #2
Check here:
Look for this heading....
To calculate the median of the light-bulb test results, use the following query:
Tom Flaherty
Birch Hill Technology Group
Look for this heading....
To calculate the median of the light-bulb test results, use the following query:
Tom Flaherty
Birch Hill Technology Group
The following will work for Oracle 8i:
select avg(q1.amount) median
from (
select amount,rownum rownbr
from (select amount
from tablex
order by 1) q0
) q1,
(
select round(count(*)/2+.4) as lowval,
round(count(*)/2+.5) as highval
from tablex
) q2
where rownbr between q2.lowval and q2.highval
/
Explanation: Q0: will retrieve all the amount values from tablex and order them
Q1: will relist the values applying the row number to the list set, (we can't do this in one step).
Q2: will retrieve the low and high row number value for the count of rows retrieved.
If an odd # then you'll be looking for rows between 13 and 13 for example.
The whole mess then (2 amount values, or one) is averaged to compute the median.
Cheers AA 8~)
select avg(q1.amount) median
from (
select amount,rownum rownbr
from (select amount
from tablex
order by 1) q0
) q1,
(
select round(count(*)/2+.4) as lowval,
round(count(*)/2+.5) as highval
from tablex
) q2
where rownbr between q2.lowval and q2.highval
/
Explanation: Q0: will retrieve all the amount values from tablex and order them
Q1: will relist the values applying the row number to the list set, (we can't do this in one step).
Q2: will retrieve the low and high row number value for the count of rows retrieved.
If an odd # then you'll be looking for rows between 13 and 13 for example.
The whole mess then (2 amount values, or one) is averaged to compute the median.
Cheers AA 8~)
- Status