Calculating Median in Grouped Contexts

In this lesson, you will learn how to calculate the median value for specific categories within your dataset using window functions.

While SQL provides easy functions like AVG() for means, there is often no built-in MEDIAN() function. To find the median—the middle value in a sorted list—within groups, we must leverage the fact that the median is simply the value at the 50th percentile of an ordered set.

The Strategy: Percentiles

To compute the median in a grouped context, we rely on the PERCENTILE_CONT(0.5) function (or PERCENTILE_DISC(0.5) for discrete values) combined with an OVER(PARTITION BY ...) clause. The PARTITION BY clause acts as our grouping mechanism, ensuring the median calculation resets for every unique category.

Imagine you have a table employee_salaries with columns department and salary. If you want to find the median salary for each department, you would write:

SELECT DISTINCT
    department,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) 
        OVER(PARTITION BY department) AS median_salary
FROM employee_salaries;

Why this works

1. PARTITION BY department: This isolates the data into individual buckets for each department, just like GROUP BY does for aggregate functions.
2. WITHIN GROUP (ORDER BY salary): This tells the database to sort the salaries within those specific partitions before looking for the middle value.
3. PERCENTILE_CONT(0.5): This instructs the database to find the continuous midpoint (the 50th percentile) of that sorted list.

Using this method, you can perform complex statistical analysis across different dimensions without writing cumbersome subqueries or manual row-counting logic. This approach is highly efficient for large datasets because the window function processes the partitioned data in memory based on the specified sort order.

Key Takeaway: To calculate a median within groups, use the PERCENTILE_CONT window function combined with a PARTITION BY clause to sort and isolate values for each specific group.