MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE instructor
SELECT 'name 1-1' name, 'dept 1' dept_name, 1 salary UNION ALL
SELECT 'name 1-2' name, 'dept 1' dept_name, 2 salary UNION ALL
SELECT 'name 1-3' name, 'dept 1' dept_name, 3 salary UNION ALL
SELECT 'name 2-1' name, 'dept 2' dept_name, 4 salary UNION ALL
SELECT 'name 2-2' name, 'dept 2' dept_name, 5 salary UNION ALL
SELECT 'name 2-3' name, 'dept 2' dept_name, 6 salary UNION ALL
SELECT 'name 3-1' name, 'dept 3' dept_name, 7 salary UNION ALL
SELECT 'name 3-2' name, 'dept 3' dept_name, 8 salary 

Records: 8  Duplicates: 0  Warnings: 0


WITH
cte1 AS (
  select dept_name, sum(salary) as sum_salary
  from instructor
  group by dept_name
),
cte2 AS (
  select avg(sum_salary) avg_sum_salary
  from cte1
)
SELECT cte1.*
FROM cte1
JOIN cte2 ON cte1.sum_salary > cte2.avg_sum_salary


dept_name
sum_salary


dept 2
15

dept 3
15


SELECT dept_name, sum_salary
FROM ( SELECT dept_name, 
              SUM(salary) sum_salary,
              SUM(SUM(salary)) OVER () / COUNT(*) OVER () avg_sum_salary
       FROM instructor
       GROUP BY dept_name ) subquery
WHERE sum_salary > avg_sum_salary


dept_name
sum_salary


dept 2
15

dept 3
15