Median in SQL

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 thoughts on “Median in SQL

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

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s