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 *
ORDER BY CASE
WHEN employee_salary >= AVG(employee_salary)
PARTITION BY Department_ID) THEN 0
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
AVG(employee_salary) OVER (PARTITION BY Department_ID) AS sal
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
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
(select departmentid, avg(salary) as avgsalary
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.
- You don't need to
departments. Just because you have the idea doesn't mean the
GROUP BYis 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 company_name, Avg( salary ) compAvgSalary
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 company_name, max( salary ) compMaxSalary
group by company_name ) PreMax
JOIN Works w
on PreMax.Company_Name = w.Company_Name
AND w.Salary = PreMax.compMaxSalary