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


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.

List of SQL Complex Operations

RankCalculates the ranking of a series of numbers
MedianCalculates the median of a series of numbers
Running TotalsCalculates the running total for a series of numbers
Percent To TotalCalculates the percent to total for each number in a series
Cumulative Percent To TotalCalculates the cumulative percent to total for each number in a series

Next: SQL Running Totals

Copyright © 2016   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact