'Find all employees who earn more than the average salary of all employees of their company

I have the following database scheema. employee (employee-name, street, city) works (employee-name, company-name, salary) company (company-name, city) manages (employee-name, manager-name)

what is the query of Find all employees who earn more than the average salary of all employees of their company from the scheema?

mysql


Solution 1:[1]

SELECT employee-name FROM works WHERE salary > (SELECT AVG(salary) FROM works);

Solution 2:[2]

select distinct employee_name

from 
(select company_name, avg(salary) as avg

from works

group by company_name; 
 
) as avg_company_salary join works on avg_company_salary.company_name = works.company_name

where works.salary > avg_company_salary.avg;

// NOTE we need to compare each employee salary with average salary of its particular company.

The inner subquery returns the table containing company_name and corresponding average salary of that company , which is then compared with each individuals salary having same company_name as that of in the avg_company_salary table.

Solution 3:[3]

Select employee-name <--use * for all details--> from works where salary > (select avg(salary) from works) group by company-name;

As we have all the required data (employee name, salary, comp name) in works table so no need of join. We can directly run the query on this table.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 forpas
Solution 2 Shrutam Jadhav
Solution 3 Rajat Arora

Related Questions