SQL Median

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,

Table Total_Sales

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

Result:

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.

Link to this page: If you find this page useful, we encourage you to link to this page. Simply copy and paste the code below to your website, blog, or profile.

<a href="http://www.1keydata.com/sql/sql-median.html">SQL Median</a>