SQL > Advanced 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

NameSales
John10
Jennifer15
Stella20
Sophia40
Greg50
Jeff20

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:

Median
20

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 © 2014   1keydata.com   All Rights Reserved.     Privacy Policy