Employees Whose Salary is Above the Average Salary of Their Department

Find Employees Whose Salary is Above the Average Salary of Their Department Query Using MS SQL Server?

To find employees whose salary is above the average salary of their department using Microsoft SQL Server, you can use a combination of window functions and subqueries. Here’s how to structure the query:

SQL Query

WITH DepartmentAverages AS (

    SELECT

        DepartmentID,

        AVG(Salary) AS AvgSalary

    FROM Employees

    GROUP BY DepartmentID

)

SELECT

    e.EmployeeID,

    e.DepartmentID,

    e.Salary

FROM Employees e

JOIN DepartmentAverages da

    ON e.DepartmentID = da.DepartmentID

WHERE e.Salary > da.AvgSalary;

Explanation:

1. Common Table Expression (CTE) DepartmentAverages:

   • SELECT DepartmentID, AVG(Salary) AS AvgSalary: Calculates the average salary for each department.
   • GROUP BY DepartmentID: Groups the results by department to get the average salary per department.

2. Main Query:

   • JOIN DepartmentAverages da ON e.DepartmentID = da.DepartmentID: Joins the Employees table with the DepartmentAverages CTE to compare each employee’s salary with their department’s average salary.
   • WHERE e.Salary > da.AvgSalary: Filters to include only those employees whose salary is higher than the average salary of their department.

Example Data and Results

Assume the Employees table has the following data:

EmployeeID	DepartmentID	Salary
1	10	60000
2	10	70000
3	10	50000
4	20	80000
5	20	75000
6	30	90000

For this data:

• Department 10 has an average salary of (60000 + 70000 + 50000) / 3 = 60000.
• Department 20 has an average salary of (80000 + 75000) / 2 = 77500.
• Department 30 has an average salary of 90000 (only one employee).

The result of the query would be:

EmployeeID	DepartmentID	Salary
2	10	70000
4	20	80000
6	30	90000

This result shows employees whose salary is above the average salary of their respective department.

You can execute this query in SQL Server Management Studio or any SQL query tool connected to your SQL Server to get the list of employees whose salaries are higher than their department's average salary.