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 theEmployees
table. - The final
SELECT
statement joins theEmployees
table with theAvgSalaries
CTE and filters employees with a salary above the average.
- The
You can run either of these queries in SQL Server Management Studio or any other SQL query tool connected to your SQL Server.
Post a Comment