Forum

Write an SQL query ...
 
Share:
Notifications
Clear all

Write an SQL query to find employees who have a higher salary than the average salary of their department.

1 Posts
1 Users
0 Reactions
1,309 Views
Posts: 69
Topic starter
(@Vinay Kumar)
Joined: 6 years ago

To find employees whose salary is higher than the average salary of their department, you can use a subquery that calculates the average salary for each department.

SQL Statement:

sql
 
SELECT employee_id, name, department_id, salary
FROM employees e
WHERE salary > (SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id);

Explanation:

  • The subquery (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) calculates the average salary for each department.
  • The WHERE clause in the main query compares the salary of each employee to the average salary of their respective department.
  • The query returns employees who earn more than the average salary of their department.

Example Scenario: Consider an employees table:

employee_idnamedepartment_idsalary
1John10180000
2Alice10290000
3Bob10185000
4Carol10395000
5Dave10175000
6Eve10288000

Query Result: This query would return:

employee_idnamedepartment_idsalary
3Bob10185000
2Alice10290000
4Carol10395000

Employees John, Dave, and Eve are excluded because their salaries are not higher than their department's average.


Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Share: