Median in SQL

Posted by

Are you stuck with a database that does not offer analytic SQL facilities ? Never mind, you can use the following trivial Smile query can determine the median salary from the EMP table

SQL> select avg(sal) from
  2  ( select x.sal
  3   from scott.EMP x, scott.EMP y
  4   group by x.sal
  5   having sum(sign(1-sign(y.sal-x.sal))) in
  6          ( trunc((count(*)+1)/2), trunc((count(*)+2)/2) ) );


Of course, if that is basically gobbledygook to you (and it certainly is to me!)…well, you should check out Oracle’s cool Analytic SQL features here


  1. That doesn’t seem to work (reliably) if you have duplicate values–if you have one or more people with the same salary. Sometimes I get nulls, and sometimes I get a value that’s off.

    It does appear to work on SCOTT.EMP as shipped by Oracle, but it returns null if you restrict it to SAL < 2000.

  2. Try this on for size:

    WITH q AS (SELECT * FROM scott.emp /* WHERE sal < 2000 */)
    SELECT avg(max(x.sal))
    FROM q x, q y
    GROUP BY x.rowid
    HAVING count(CASE
    WHEN x.sal < y.sal THEN 1
    WHEN x.sal = y.sal AND x.rowid < y.rowid THEN 1
    BETWEEN count(*)/2 – 1 AND count(*)/2

  3. And if you wanted something a little more vaguely understandable (but what’s the fun in that?…):

    WITH q AS (SELECT * FROM scott.emp /* WHERE sal < 2000 */)
    SELECT avg(x.sal)
    FROM q x
    WHERE (
    SELECT count(*)
    FROM q y
    WHERE x.sal < y.sal
    OR x.sal = y.sal
    AND x.rowid < y.rowid
    ) BETWEEN (SELECT count(*)/2-1 FROM q) AND (SELECT count(*)/2 FROM q)

Leave a Reply to Jason Bucata Cancel reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.