Get Employees Who Are Below Average Salary After A Raise

1
votes

I need to get fname, lname, salary of employees who are $400.00 below the average salary even after geting a 10% salary raise.

I'm able to get employees who's salary is below the average salary, but am unsure how to get those who are $400 below after a raise.

I'm using MySQL. Thank you.

select AVG(salary) from employee; #gives me the average salary

select Fname, Lname, Salary, 1.10*Salary as NewSalary
from employee; 
#gives me names, old salary and salary raised.

This gives me the employees with salary less than the average salary:

select Fname, Lname, Salary
from employee
where Salary < (select AVG(salary) from employee);

I was thinking something like this, but this doesn't work; unknown column newsalary:

select Fname, Lname, Salary, 1.10*Salary as NewSalary
from employee
where NewSalary - (select AVG(salary) from employee) = 400 ;
3
mysqlsqldatabaseselectwhere-clause
You cannot use column aliases in the WHERE. Instead of the alias repeat the actual expression like ...WHERE 1.10 * Salary - (... - sticky bit

3 Answers

1
votes

You have the right idea, you just can't use aliases in the where clause like that. Just use the formula directly, and you should be fine. Also, you should probably use <=, and not =:

select Fname, Lname, Salary, 1.10 * Salary as NewSalary
from   employee
where  1.10 * Salary - (select AVG(salary) from employee) <= 400;
0
votes

select Fname, Lname, Salary, 1.10 * Salary as NewSalary from employee where ((1.10 * Salary) - (select AVG(salary) from employee)) <= 400;

0
votes

You can do this with one statement by using HAVING

select Fname, Lname, AVG(salary * 1.10)    
FROM employee
GROUP BY FNAME, LNAME 
HAVING (AVG(salary) * 1.10) = 400

But I don't understand the concept of average salary of an employee in a table. You could be looking for an average salary of a group of employees perhaps? Or maybe you are storing historical values of salaries, but again I see no purpose of calculating average of that.