Calculate Median Salary in SQL using PERCENTILE_CONT and PERCENTILE_DISC

Analyze Data Using PERCENTILE_CONT and PERCENTILE_DISC

Write a SQL query to calculate the median salary within each department using PERCENTILE_CONT and PERCENTILE_DISC.

Solution:

-- Calculate the median salary within each department.
SELECT 
    DepartmentID,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary) OVER (PARTITION BY DepartmentID) AS MedianCont,
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY Salary) OVER (PARTITION BY DepartmentID) AS MedianDisc
FROM Employees;

Explanation:

1. Purpose of the Query :

1. The goal is to demonstrate how to use PERCENTILE_CONT (continuous) and PERCENTILE_DISC (discrete) to calculate the median salary within each department.

2. Key Components :

1. PERCENTILE_CONT(0.5) : Calculates the continuous median (interpolated value).
2. PERCENTILE_DISC(0.5) : Calculates the discrete median (actual value).
3. WITHIN GROUP (ORDER BY Salary) : Specifies the ordering for percentile calculations.
4. PARTITION BY DepartmentID : Groups data by department.

3. Why use PERCENTILE Functions? :

1. These functions are essential for statistical analysis, such as identifying central tendencies.

4. Real-World Application :

1. For example, in HR systems, you might use this query to analyze salary distributions within departments.

Additional Notes:

• PERCENTILE_CONT interpolates values, while PERCENTILE_DISC selects actual values.
• Use this exercise to teach how to perform advanced statistical analysis.

For more Practice: Solve these Related Problems:

• Write a SQL query to calculate the median test score for each subject using PERCENTILE_CONT.
• Write a SQL query to determine the discrete median age of users in each region using PERCENTILE_DISC.
• Write a SQL query to analyze the median response time for support tickets across different teams.
• Write a SQL query to compute the continuous and discrete medians of house prices in various neighborhoods.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Use FOR XML PATH to Generate XML Output.
Next SQL Exercise: Use CROSS APPLY with Table-Valued Functions.