Employees with higher salaries than their high school?
I have a table called employees in which I have a name, department_id and salary. I want to find employees whose salary is above the average for their department and see their names, department_id, salary and average salary for their department. I wrote this code but it doesn't work. How can we fix this? Thanks in advance.
SELECT name, department_id, salary, avg(salary)
FROM employees
GROUP BY name, department_id, salary
HAVING salary > (select avg(salary) from employees group by department_id)
I updated my code as you said:
SELECT department_id, salary, avg(salary), count(*)
FROM employees e
GROUP BY department_id, salary
HAVING salary > (select avg(salary) from employees e2 where e2.department_id=e.department_id)
But when I run this I get this result:
You can see wages and averages are the same and there are 2 departments from the 80s, I need 1 of all existing departments. How can we fix this. I am using an Oracle database if that matters. Thank.
source to share
Your code is pretty close. But instead of group by
in a subquery, it should be matched against the outer query. And you don't need external aggregation, just a suggestion where
:
SELECT name, department_id, salary
FROM employees e
WHERE salary > (select avg(salary) from employees e2 where e2.department_id = e.department_id);
However, you seem to be learning Oracle. You can also write this query using analytic functions:
select e.*
from (select e.*, avg(salary) over (partition by department) as avgsalary
from employees e
) e
where e.salary > e.avgsalary;
While this is probably a little advanced for what you are learning, I recommend that you understand both queries.
source to share