## AdBlock Detected!

Our website is made possible by displaying ads to our visitors. Please supporting us by whitelisting our website.

## SQL Median |

SQL > Advanced SQL >
Median
Median refers to the "middle" number in a series of numbers. When the total count is odd, this is pretty straightforward. Assuming there are When the total count is even, there is no number that represents the exact "middle" number. In this case, we would take the average of the two numbers closest to the middle. Assuming there are Given the above definition, our strategy for calculating the median is as follows: - Sort the numbers in order. It doesn't matter whether we sort ascending or descending, as both would lead to the same answer.
- Use
**CASE**to determine which two ranks we should take the average of, based on the rules above. - Calculate the average value of the two numbers chosen. This is the median we are looking for.
This is a fairly complex query as we will need to include a number of elements: - To resolve the odd number vs even number, we will use the
**CASE statement**. - To get the rank of each number, we will use a
**window function**. - To calculate the different components, we will need to use
**CTE**.
## An Example of Calculating MedianLet's use an example to illustrate. Say we have the following table, Table
the SQL we would use is, With Count_Total as
(SELECT COUNT(*) row_count from Total_Sales) , From_To as (SELECT CASE WHEN row_count % 2 = 1 THEN (row_count+1)/2 ELSE row_count/2 END as begin_count, CASE WHEN row_count % 2 = 1 THEN (row_count+1)/2 ELSE (row_count+2)/2 END as end_count From Count_Total) SELECT AVG(Sales) Median from (SELECT Name, Sales, ROW_NUMBER() OVER (ORDER BY Sales ASC) RK FROM Total_Sales) WHERE RK BETWEEN (SELECT begin_count FROM From_To) and (SELECT end_count FROM From_To); Result:
21 is the average of 22, the third largest number, and 20, the fourth largest number. ## Explanation of SQL CodeThe first CTE, Count_Total, calculates the total number of rows in the table. The second CTE, From_To, calculates the higher rank and the lower rank values that we will use to calculate the median. The main SQL statement uses a window function with ## Second Example of Calculating MedianLet's change the table content by adding a row so the table is as follows: Table
Now the above SQL would yield the following result:
In this case 22 is the 4th largest number and also the 4th smallest number in the table, hence it is the median. ## List of SQL Complex Operations
SQL Running Totals |

Our website is made possible by displaying ads to our visitors. Please supporting us by whitelisting our website.