rewrite sql without with clause

Question

I've fallen down the SQL rabit hole, please help me I need to rewrite this query without the with clause

with dept_total(dept_name, value) as
     (select dept_name, sum(salary)
     from instructor
     group by dept_name),
dept_total_avg(value) as 
     (select avg(value)
     from dept_total)
select dept_name
from dept_total, dept_total_avg
where dept_total.value >= dept_total_avg.value;

basically the first table adds up all salaries based on department. the second table gets an average of the sums. I need to find a way to pick out the departments that have a department total greater than the averages.

It can be written any way as long as it doesn't use the with clause. I've been thinking about - select sum(salary)/count(salary) as dept_total_avg, dept_name from instructor where dept_total_avg > all (select sum(salary), from instructor) group by dept_name;

but it doesn't work and now my brain doesn't either. Please help.

Answer 1

You need to calculate the department average without access to the intermediate cte dept_total

SELECT
    dept_total.dept_name, dept_total.value, cj.dept_av
FROM (
    SELECT
        dept_name, SUM(salary) value
    FROM instructor
    GROUP BY
        dept_name
    ) AS dept_total
CROSS JOIN (
        SELECT
            SUM(salary) / (COUNT(DISTINCT dept_name) * 1.0) dept_av
        FROM instructor
    ) cj
WHERE dept_total.value >= cj.dept_av
;

In the original query there are 2 "common table expressions" (cte) and each one is given a name so that they can be referred to later.

The first of these is given the name dept_total

Any following cte is allowed to reuse that cte by that name. However this reuse aspect of cte's is not possible with traditional "derived table" subqueries. Hence where the name dept_total is used in the final query you have to substitute a new way to arrive at the departmental average.

---

In case you are not aware, MySQL 8 is now available and it supports "common table expressions" (the with clause)