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
todepartments
. Just because you have the idea doesn't mean thejoin
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
Related Topics
Mysql in Xampp Starts and Stops in Five Seconds
Error:More Than One Row Returned by a Subquery Used as an Expression
Remove Multiple Keys from Jsonb Column in One Statement
Mysql - Move Rows from One Table to Another
Alternative to Row_Number() to Get Row Position
Remove Numbers from String SQL Server
How to Merge Rows on Specific Condition
Select Ids from Multiple Rows Where Column Values Satisfy One Condition But Not Another
Sql Select All Data That Past 45 Days
Checking a Column If It Contains a Row Value
How to Split One Column into Two Columns in SQL Server
How to Remove Special Characters in Column With MySQL
Sql: How to Get Both Match and Non-Match Records
1052: Column 'Id' in Field List Is Ambiguous
Mysql - Operand Should Contain 1 Column(S)