To get the median, we need to be able to accomplish the following:
- Sort the rows in order and find the rank for each row.
- Determine what is the "middle" rank. For example, if there are 9 rows, the middle rank would be 5.
- Obtain the value for the middle-ranked row.
Let's use an example to illustrate. Say we have the following table,
we would type,
SELECT Sales Median FROM
(SELECT a1.Name, a1.Sales, COUNT(a1.Sales) Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales < a2.Sales OR (a1.Sales=a2.Sales AND a1.Name <= a2.Name)
group by a1.Name, a1.Sales
order by a1.Sales desc) a3
WHERE Rank = (SELECT (COUNT(*)+1) DIV 2 FROM Total_Sales);
You will find that Lines 2-6 are the same as how we find the rank of each row. Line 7 finds the "middle" rank. DIV is the way to find the quotient in MySQL, the exact way to obtain the quotient may be different with other databases. Finally, Line 1 obtains the value for the middle-ranked row.
Next: SQL Running Totals
|| Copyright © 2013 1keydata.com All Rights Reserved.