Mysql Query Find all the departments that make an average salary greater than the average salary of-CodePudding

I am trying to Find all the departments that make an average salary greater than the average salary of instructors working in the Finance department.

the tables are; department(dept_name,building,budget) instructor(ID,name,dept_name,salary)

            SELECT 
    *
FROM
    instructor e
        JOIN
    department ON e.dept_name = e.dept_name
WHERE
    salary > (SELECT 
            AVG(salary)
        FROM
            instructor e2
        WHERE
            dept_name = 'Finance');

CodePudding user response：

We can try an aggregation approach here:

SELECT d.dept_name
FROM department d
INNER JOIN instructor i
    ON i.dept_name = d.dept_name
GROUP BY d.dept_name
HAVING AVG(i.salary) > (SELECT AVG(salary) FROM instructor WHERE dept_name = 'Finance');