Learning is not a spectator sport

Median in SQL

1–2 minutes

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) ) );

  AVG(SAL)
----------
      1550

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

3 responses to “Median in SQL”

  1. Jason Bucata Avatar
    Jason Bucata

    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.

    Reply
  2. Jason Bucata Avatar
    Jason Bucata

    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
    END)
    BETWEEN count(*)/2 – 1 AND count(*)/2

    Reply
  3. Jason Bucata Avatar
    Jason Bucata

    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)

    Reply

Leave a reply to Jason Bucata Cancel reply

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

Trending