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

 Name Sales John 10 Jennifer 15 Stella 20 Sophia 40 Greg 50 Jeff 20

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.

### List of SQL Complex Operations

 Operation Description Rank Calculates the ranking of a series of numbers Median Calculates the median of a series of numbers Running Totals Calculates the running total for a series of numbers Percent To Total Calculates the percent to total for each number in a series Cumulative Percent To Total Calculates the cumulative percent to total for each number in a series

Next: SQL Running Totals

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