Are you stuck with a database that does not offer analytic SQL facilities ? Never mind, you can use the following trivial 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

Advertisements

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.

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

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)