I have a table called Graduates recording name and income for different graduates. Now I need to calculate the median of income. Here is the code from a book.
My question is
- What is the result from having clause?
- What is the result from self join ?
SELECT AVG(DISTINCT income)
FROM (
SELECT T1.income
FROM Graduates T1, Graduates T2
GROUP BY T1.income
HAVING SUM(CASE WHEN T2.income >= T1.income THEN 1 ELSE 0 END)
>= COUNT(*) / 2
AND SUM(CASE WHEN T2.income <= T1.income THEN 1 ELSE 0 END)
>= COUNT(*) / 2
) TMP;