How do you select employees whose salary is above the average salary of their department?

Question

Khushi Singh · Answer

To find employees whose salary is above the average for their department, you can use a correlated subquery in SQL. This type of subquery checks each employee’s salary against the average salary calculated for their specific department. It runs for each employee in the main query, allowing a focused comparison based on department averages.

Here's how it works: for each employee in the Employees table, we compare their salary to the average salary of everyone in their department. If their salary is higher, they show up in the results.

Let’s say the Employees table has these columns: EmployeeID, Name, DepartmentID, and Salary.

The SQL query would look like this:

SELECT EmployeeID, Name, DepartmentID, Salary
FROM Employees E
WHERE Salary > (
   SELECT AVG(Salary)
   FROM Employees
   WHERE DepartmentID = E.DepartmentID
);

In this example, the main query selects employee info, while the subquery figures out the average salary for that employee’s department (E.DepartmentID). Since the subquery refers to a column from the main query, it calculates the average for each department separately.

This approach makes sure we only compare each employee's salary to their department's average, not the whole company’s average. It’s a straightforward way to pick out top earners within each department, which can help with performance assessments, pay reviews, or decisions about promotions.

forum

How do you select employees whose salary is above the average salary of their department?

Can you answer this question?

1 Answers