How to extract median value?

Question

I need to get median value in column "median". Any ideas, please?

SELECT
MIN(score) min, CAST(AVG(score) AS float) median, MAX(score) max
FROM result JOIN student ON student.id = result.student_id

Answer 1

I think the simplest method is PERCENTILE_CONT() or PERCENTILE_DISC():

SELECT MIN(score) as min_score,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY score) as median_score,
       MAX(score) max_score
FROM result r JOIN
     student s
     ON s.id = r.student_id;

This assumes (reasonably) that score is numeric.

The difference between PERCENTILE_CONT() and PERCENTILE_DISC() is what happens when there are an even number of values. That is usually an unimportant consideration, unless you have a small amount of data.

Answer 2

Average is not Median, you're right. You can do it the exact way, with:

SELECT (  (SELECT MIN(score) FROM Results X
           WHERE (SELECT COUNT(*) FROM Results Y WHERE Y.score<= X.score)
                  >= (SELECT COUNT(*) FROM Results ) / 2)
        + (SELECT MAX(num) FROM Results X
           WHERE (SELECT COUNT(*) FROM Results Y WHERE Y.score >= X.score)
                  >= (SELECT COUNT(*) FROM Results ) / 2)
       ) / 2 AS median

This handles the case where the boundary between the upper and lower 50% falls between two values; it arbitrarily takes the halfway point between them as the median. There are arguments why that might be weighted slightly higher or lower, but any value in that interval correctly divides the population in two.

Or, if you are dealing with a hyperbolic distribution, there's a short-cut approximation:

SELECT SQRT(SUM(num) / SUM(1.0/num)) FROM List

Many other real-world distributions have a lot of little members and a few large members. Having just hit SAVE and seen the prior answer: yes, SQL2003 now gives you something simpler :-)