CODEWITHSUNDEEP
sqloracle-database
user3704771
user3704771

Reputation: 7

Oracle SQL - calculate avg pay in department and select all above avg

First, i am sorry about my english.

I have 2 tables

EMPLOYEE

DEPARTMENT

i have more tables, but for my question it is enough.

and i want select every employee, who has above avg pay in his/her department.

for example, Jane and John are in Administration department and Jane has 200$ and John has 400$. And Sarah and Kelly are in Shipping department and Sarah has 300$ and Kelly has 400$.

output

John | Administration | 400

Kelly | Shipping | 400

i stuck on this

select name, department_name, pay
from EMPLOYEE
join DEPARTMENT using (department_id)
where pay >= (select avg(pay) from EMPLOYEE, DEPARTMENT
where EMPLOYEE.department_id = ??);

thanks a lot for your answers.

Upvotes: 0

Views: 83

Answers (2)

CSharper
CSharper

Reputation: 5580

You have to link the inner subquery to the outter query, remember: subqueries are ran for every record, so they should generally be avoided. Try this out.

select name, department_name, pay
from EMPLOYEE e
join DEPARTMENT using (department_id)
where pay >= (select avg(pay) from EMPLOYEE e2, DEPARTMENT
where e2.department_id = e.department_id);

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20804

This will get you started. It is not intended to be a complete answer.

select name, department, pay, avergePay
from EMPLOYEE
join DEPARTMENT using (department_id)
join (
select department_id, avg(pay) averagePay
from EMPLOYEE
join DEPARTMENT using (department_id)
) temp using (department_id)

Upvotes: 1

Related Questions