CODEWITHSUNDEEP
sqlunion
user3159879
user3159879

Reputation: 61

How can I improve this SQL code?

This is my SQL problem. My SQL table "Employer" has 3 columns: EmployerName, Salary, WorkPosition(eg. Worker, Developer, Manager). So how can I show all the Employers whose salary is higher than the average salary of all the one working in the same position.

I did it as get the values for each position and show out all of those by using UNION. It worked, but seems not to be a good solution.

(Select Employername 
From Employer
Where WorkingPosition = 'Worker' 
and Salary > (Select avg(Salary) from Employer
              Where WorkingPosition = 'Worker')) 
UNION
(Select Employername 
From Employer
Where WorkingPosition = 'Developer' 
and Salary > (Select avg(Salary) from Employer
              Where WorkingPosition = 'Developer')) 
UNION
(Select Employername 
From Employer
Where WorkingPosition = 'Manager' 
and Salary > (Select avg(Salary) from Employer
              Where WorkingPosition = 'Manager'))

Thanks

Upvotes: 2

Views: 70

Answers (4)

user330315
user330315

Reputation:

You didn't state your DBMS, the following is ANSI SQL.

It will first calculate the averages once for all working positions you are interested in. As that result is quite small it is very likely that the database server can keep that in memory. So the subsequent comparisons in the outer (final) query don't need to re-compute the average each time (of course this also depends on the DBMS being used)

with avg_sales as (
  Select WorkingPosition, avg(Salary) as avg_salary
  from Employer
  Where WorkingPosition in ('Worker', 'Developer', 'Manager')
  group by WorkingPosition
)
Select emp.Employername 
From Employer emp
where Salary > (select avg_salary
                from avg_sales s
                where s.WorkingPosition = emp.WorkingPosition)
 and WorkingPosition in ('Worker', 'Developer', 'Manager');

Upvotes: 2

StephaneM
StephaneM

Reputation: 4899

Get them all at once:

Select e1.Employername 
From Employer e1
Where 
e1.Salary > (Select avg(e2.Salary) from Employer e2
              Where e2.WorkingPosition = e1.WorkingPosition))

Upvotes: 1

lc.
lc.

Reputation: 116498

You should be able to join on a derived table with the averages.

The best way to solve this type of problem is to break it down into parts. The first part being how to get the average salary for each position. You should be able to GROUP to get this:

SELECT AVG(Salary) AS AvgSalary, WorkingPosition
FROM Employer
GROUP BY WorkingPosition

Then you can join on this table - now you have each employee correlated with the average salary in their position:

SELECT *
FROM Employer
INNER JOIN
(
    SELECT AVG(Salary) AS AvgSalary, WorkingPosition
    FROM Employer
    GROUP BY WorkingPosition
) Averages ON Employer.WorkingPosition = Averages.WorkingPosition

Finally, add the condition:

SELECT Employer.Employername
FROM Employer
INNER JOIN
(
    SELECT AVG(Salary) AS AvgSalary, WorkingPosition
    FROM Employer
    GROUP BY WorkingPosition
) Averages ON Employer.WorkingPosition = Averages.WorkingPosition
WHERE Employer.Salary > Averages.AvgSalary

If you are more comfortable working with a subquery in the WHERE clause, you can do this as well. I would imagine both should perform rather similarly:

SELECT Employername
FROM Employer
WHERE Salary > (SELECT AVG(Salary) 
                FROM Employer AS Averages 
                WHERE Employer.WorkingPosition = Averages.WorkingPosition)

Upvotes: 2

René Nyffenegger
René Nyffenegger

Reputation: 40499

Select Employername From Employer
Where 
 ( WorkingPosition = 'Worker' and Salary > 
     (Select avg(Salary) from Employer Where WorkingPosition = 'Worker') 
 )
 or 
 ( WorkingPosition = 'Developer' and Salary > 
    (Select avg(Salary) from Employer Where WorkingPosition = 'Developer')
 ) 
 or
 ( WorkingPosition = 'Manager' and Salary > 
    (Select avg(Salary) from Employer Where WorkingPosition = 'Manager')
 )

Or

select o.employername 
  from employer o
where
  o.salary > (select avg(i.salary)
                from employer i
               where i.workingposition = o.workingposition
             )
     and
  o.workingposition in ('Worker', 'Developer', 'Manager')

Upvotes: 1

Related Questions