How to use a column calculated in the WHERE clause of SQL?

Asked

Viewed 55 times

-2

I have the table Employees who own a field called salary.

I need to list only employees who are below the average of all employees' salaries registered in the database.

My SQL command is like this at the moment:

SELECT 
e.IdDepto,
d.NomeDepto,
COUNT(*) as 'Total Empregados',
AVG(e.Salario) AS 'Média'
FROM 
Empregado e
inner JOIN Departamento d ON (d.IdDepto = e.IdDepto)
WHERE
e.Salario < ???
GROUP BY e.IdDepto, d.NomeDepto

My doubt is exactly in these ??. How can I take the value of the calculated column called 'Average' and use it as a parameter to display only the salaries of employees who are below its value?

sql

1 answer

0


You can do it using a new query, where the result of it will be only the average of wages.

SELECT AVG(Salario) FROM Empregado

In the main query it would be, reduced

SELECT 
  *
FROM 
  Empregado e
WHERE
  e.Salario < (SELECT AVG(Salario) FROM Empregado)

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.