Statistical Median Function in a Query

[DMHCCI]

I am looking for a median function built in to a query such as:

SELECT median(number_list) AS medianoflist
FROM basequery;

Access has Min, Max, Avg etc.

Is there a way to run a query and get median - which is the middle number of a list:

Example:
1 6 8 10 11

8 is the median (middle number of the list) if odd

Example 2:

1 3 5 7 9 10 12 15

(7 + 9)/2 = 8 is the median if even number in list

 

[PHV]

You may try something like this:
SELECT Avg([number]) AS median_number
FROM (
SELECT Max(number_list) AS [number] FROM (SELECT TOP 50 PERCENT number_list FROM basequery ORDER BY 1 ASC) AS A
UNION SELECT Min(number_list) FROM (SELECT TOP 50 PERCENT number_list FROM basequery ORDER BY 1 DESC) AS B
) AS C

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886