Round #670

Find the median salary in each department using window functions.

To find the median salary in each department using window functions in SQL, you can use the `PERCENTILE_CONT` function. This function calculates a percentile based on a continuous distribution of values. Here's an example query that demonstrates how to calculate the median salary for each department: ```sql SELECT DepartmentID, EmployeeName, Salary, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary) OVER (PARTITION BY DepartmentID) AS MedianSalary FROM Employees; ``` ### Explanation: - `DepartmentID`: The ID of the department. - `EmployeeName`: The name of the employee. - `Salary`: The salary of the employee. - `PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary) OVER (PARTITION BY DepartmentID) AS MedianSalary`: This calculates the median salary for each department. - `PERCENTILE_CONT(0.5)`: Calculates the 50th percentile, which is the median. - `WITHIN GROUP (ORDER BY Salary)`: Orders the salaries within each department. - `OVER (PARTITION BY DepartmentID)`: Partitions the data by department. ### Notes: - The `PERCENTILE_CONT` function returns a continuous value. If you need an exact integer value, you might need to round it using the `ROUND` function. ```sql SELECT DepartmentID, EmployeeName, Salary, ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary) OVER (PARTITION BY DepartmentID), 2) AS MedianSalary FROM Employees; ``` This query will give you the median salary for each department, rounded to two decimal places. ### Example Data: Assume your `Employees` table has the following data: | EmployeeName | DepartmentID | Salary | |--------------|--------------|--------| | Alice | 1 | 5000 | | Bob | 1 | 6000 | | Charlie | 2 | 4500 | | David | 2 | 5500 | | Eve | 3 | 7000 | ### Result: The query will return: | DepartmentID | EmployeeName | Salary | MedianSalary | |--------------|--------------|--------|--------------| | 1 | Alice | 5000 | 5500 | | 1 | Bob | 6000 | 5500 | | 2 | Charlie | 4500 | 5000 | | 2 | David | 5500 | 5000 | | 3 | Eve | 7000 | 7000 | This shows the median salary for each department.

To find the median salary in each department using window functions, you can use SQL Server's `PERCENTILE_CONT` function. Below is a complete example of how to achieve this: ### SQL Query ```sql SELECT DepartmentID, EmployeeName, Salary, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary) OVER (PARTITION BY DepartmentID) AS MedianSalary FROM Employees; ``` ### Explanation 1. **SELECT Clause**: - `DepartmentID`: The ID of the department. - `EmployeeName`: The name of the employee. - `Salary`: The salary of the employee. - `PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary) OVER (PARTITION BY DepartmentID) AS MedianSalary`: This calculates the median salary for each department. 2. **FROM Clause**: - `Employees`: The table containing employee data. 3. **OVER Clause**: - `PARTITION BY DepartmentID`: This partitions the data by department, so the median is calculated separately for each department. - `ORDER BY Salary`: This orders the salaries within each partition (department) before calculating the median. ### Example Data Assume you have an `Employees` table with the following structure and data: | EmployeeName | DepartmentID | Salary | |--------------|--------------|--------| | Alice | 1 | 5000 | | Bob | 1 | 6000 | | Charlie | 2 | 4500 | | David | 2 | 5500 | | Eve | 3 | 7000 | ### Result The query will return: | DepartmentID | EmployeeName | Salary | MedianSalary | |--------------|--------------|--------|--------------| | 1 | Alice | 5000 | 5500 | | 1 | Bob | 6000 | 5500 | | 2 | Charlie | 4500 | 5000 | | 2 | David | 5500 | 5000 | | 3 | Eve | 7000 | 7000 | ### Explanation of the Result - For DepartmentID 1, the median salary is 5500 (the average of 5000 and 6000). - For DepartmentID 2, the median salary is 5000 (the average of 4500 and 5500). - For DepartmentID 3, there is only one employee, so the median salary is 7000. This approach ensures that you can efficiently calculate the median salary for each department using window functions in SQL Server.