﻿ Select Every Employee That Has a Higher Salary Than the Average of His Department - ITCodar

# Select Every Employee That Has a Higher Salary Than the Average of His Department

## Employee that has a higher salary than the AVERAGE of his department - optimized

Can we do it without a subquery?

Not that I can think of. Had the condition been >= then the following would have worked

SELECT TOP 1 WITH TIES *
FROM employee
ORDER BY CASE
WHEN employee_salary >= AVG(employee_salary)
OVER (
PARTITION BY Department_ID) THEN 0
ELSE 1
END

But this is not an optimisation and it won't work correctly for the > condition if no employee has a salary greater than the average anyway (i.e. all employees in a department had the same salary)

Can we optimize it further?

You could shorten the syntax a bit with

WITH T AS
(
SELECT *,
AVG(employee_salary) OVER (PARTITION BY Department_ID) AS sal
FROM employee
)
SELECT *
FROM T
WHERE employee_salary > sal

but it still has to do much the same work.

Assuming suitable indexes on the base table already exist then the only way of avoiding some more of that work at SELECT time would be to pre-calculate the grouped SUM and COUNT_BIG in an indexed view grouped by Department_ID (to allow the average to be cheaply derived) .

## Write a SQL code to select all employees with salary greater than the average department salary and also greater than \$30K

This may work

select s1.employeeid, s1.departmentid, s1.salary
from salary as s1
inner join
(select departmentid, avg(salary) as avgsalary
from salary
group by departmentid) as s2
on s1.departmentid = s2.departmentid
where salary > avgsalary and salary > 30000
;

## SQL - Trying to get employees with greater than average department salary

Window functions are probably the best solution -- as in @zealous's answer. However, a subquery is also very reasonable. The idea is:

select e.first_name, e.salary, e.department_id
from employees as e
where e.salary > (select avg(e2.salary)
from employees e2
where e2.department_id = e.department_id
);

Here is a db<>fiddle.

Key ideas:

• You don't need to join to departments. Just because you have the idea doesn't mean the join is necessary.
• The GROUP BY is unnecessary in the subquery. The correlation clause takes care of that.

## I need to find employee who earn highest salary more than average salary each of their companies

Don't apply your condition in the HAVING, but start with that. You want the average salary per company correct? So, if your data represents 20 companies, you want each employee within their respective company that has a salary higher than the average for that company. So I first have the average per company, then join back to works and grab only those employees who have salary on-their-own higher than the average per same company.

select
PreAvg.Company_Name,
PreAvg.compAvgSalary,
w.Employee_Name,
w.Salary
from
( select company_name, Avg( salary ) compAvgSalary
from works
group by company_name ) PreAvg
JOIN Works w
on PreAvg.Company_Name = w.Company_Name
AND w.Salary = PreAvg.compAvgSalary

If you are looking for the HIGHEST Salary per company, that would be slightly different. Select the MAXIMUM salary per company, then get only those employees that have that salary for that company. Again, similar query.

select
PreMax.Company_Name,
PreMax.compMaxSalary,
w.Employee_Name,
w.Salary
from
( select company_name, max( salary ) compMaxSalary
from works
group by company_name ) PreMax
JOIN Works w
on PreMax.Company_Name = w.Company_Name
AND w.Salary = PreMax.compMaxSalary