CODEWITHSUNDEEP
sqloracle-database
user1576086
user1576086

Reputation:

Department names(with employee name) with more than 2 employee and salary greater than 90% of respective department average salary

I am trying to generate a SQL query to find the Department names(with employee name) with more than 2 employee whose salary greater than 90% of respective department average salary. My SQL Code is working fine , it has no Syntax errors but the output is giving me additional data. The table is as follos

JONES         ACCOUNTING    3000
STEEL         ACCOUNTING    2500
WILSON        RESEARCH      3000
WOLFE         RESEARCH      2500
LEE           RESEARCH      2400
LANCASTER     SALES         2000
JACKSON       SALES         2500
FISHER        SALES         3000
ADAMS         IT            2000
MILLER        IT            1000
SCOTT         IT            2500
SMITH         IT            2900
KING          EXECUTIVE     5000
JOST          EXECUTIVE     4500
CLARK         EXECUTIVE     4000

My code is as follows.

Select department_name , employee_name 
from department d , employee e 
where e.department_id = d.department_id
and (SELECT COUNT(*) 
     FROM Employee E 
     WHERE E.department_ID = D.department_ID) > 2    
    and salary > 
    0.9*(SELECT ROUND(AVG(salary),2)
    FROM employee e_inner
    WHERE e_inner.department_id = e.department_id);

I notice that my code returns the value of department with more than 2 employees and salary > 90% of department's average salary. whereas I am looking for departments with more than 2 employees whose salary is more than 90% of department avg salary

Upvotes: 1

Views: 7096

Answers (2)

Puneet Agrawal
Puneet Agrawal

Reputation: 11

The below stated query will give result as desired without using any analytics. Simple & Easy just like English.

**

  • *
  • with T as (select empname, deptno, salary from employee e1 where
    salary > (select avg(salary)*0.9 from employee e2 where
    e2.deptno=e1.deptno group by deptno having count(empname)>2)) select T.empname, deptname, salary from T, department where
    T.deptno=department.deptno;

*

**

I executed this query successfully on Oracle database.

Upvotes: 0

user330315
user330315

Reputation:

I think this should do it:

select *
from (
  select department_name, 
         employee_name,
         sum(case when salary > avg_dept_sal * 0.9 then 1 else 0 end) over (partition by department_id) as greater_count
  from (
     select d.department_name,
            e.department_id,
            e.employee_name,
            e.salary,
            count(*) over (partition by e.department_id) as dept_count,
            avg(salary) over (partition by e.department_id) as avg_dept_sal
     from employee e
       join department d on e.department_id = d.department_id
  ) t1
) t2
where greater_count  >= 2

This will return all employees of those departments. If you only want to see those employees whose salary is actually greater than the 90% you need to add another condition to the outer where clause to only select those.

Upvotes: 1

Related Questions