Find Employees with a Salary Above the Average in Their Department MS SQL server Query?

To find employees with a salary above the average salary in their department using Microsoft SQL Server, you can use a common table expression (CTE) or a subquery. Here's a step-by-step guide with SQL code:

1. Using a Subquery

Assume you have a table called Employees with columns EmployeeID, DepartmentID, and Salary.

SELECT e.EmployeeID, e.DepartmentID, e.Salary

FROM Employees e

WHERE e.Salary > (

    SELECT AVG(e2.Salary)

    FROM Employees e2

    WHERE e2.DepartmentID = e.DepartmentID

);

2. Using a Common Table Expression (CTE)

CTEs can make your query more readable. Here’s how you can use a CTE to achieve the same result:

WITH AvgSalaries AS (

    SELECT DepartmentID, AVG(Salary) AS AvgSalary

    FROM Employees

    GROUP BY DepartmentID

)

SELECT e.EmployeeID, e.DepartmentID, e.Salary

FROM Employees e

JOIN AvgSalaries a ON e.DepartmentID = a.DepartmentID

WHERE e.Salary > a.AvgSalary;

Explanation

• Subquery Method:

  • The subquery calculates the average salary for each department.
  • The outer query selects employees whose salary is higher than the average salary of their department.

• CTE Method:

  • The AvgSalaries CTE calculates the average salary for each department and is used to join with the Employees table.
  • The final SELECT statement joins the Employees table with the AvgSalaries CTE and filters employees with a salary above the average.

You can run either of these queries in SQL Server Management Studio or any other SQL query tool connected to your SQL Server.