SQL: How to use a correlation name of a subquery in FROM clause in another subquery in following WHERE clause

Consider instructor relation in university_book database.

This is the schema of instructor relation: instructor (id, name, dept_name, salary).

Here is the problem:

Find all departments where the total salary is greater than the average of the total salary at all departments.

I have written following query:

select dept_name
from
(
  select dept_name, sum(salary) as sum_salary
  from instructor
  group by dept_name
) T
where T.sum_salary > (select avg(sum_salary) from T);

I am trying to use T (correlation name) declared in FROM clause in the following WHERE clause to calculate average of the sum of salaries (sum of salaries is calculated in the FROM clause subquery before).

But I am getting below error:

Error Code: 1146. Table 'university_book.t' doesn't exist

I was able to solve it by replacing T in the WHERE clause subquery with the same subquery of FROM clause:

select dept_name
from
(
  select dept_name, sum(salary) as sum_salary
  from instructor
  group by dept_name
) T
where T.sum_salary > (select avg(sum_salary) from 

(
  select dept_name, sum(salary) as sum_salary
  from instructor
  group by dept_name
) S


);

Is there a way to access correlation names as table names in subsequent queries?

>Solution :

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

how to solve it without using WITH. – VIKKAS GUPTA

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

fiddle