in sql course by mosh. in co related subquery. to find the salary greater than the avg salary in each dept to get this query correlated subquery need to be used. so it calculates avg salary of the employees of same dept. why don’t select avg(salary) from employees group by office_id works whats wrong with it.
SELECT *
from employees e
where salary> (select avg(salary)
from employees where office_id = e.office_id)
and
use sql_hr;
select *
from employees
where salary > (select avg(salary)
from employees
order by office_id ) i got almost same output one row value is different explain to me anyone pls!
The first query in your reply selects employees with a salary greater than the average salary of their office.
The second query selects employees with a salary greater than the average of the whole company. The order by clause in the subquery doesn’t make sense since the subquery returns just a single row.