Transferring SQL query from with clause to without with clause-CodePudding

Consider the query:

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

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;

Rewrite this query without using the with construct.

The query is based on University schema which is provided by The database system concept by Korth. I assume I need to consider only the instructor table to find the answer of the query.

Instructor (ID, name, dept_name, salary)

I can found the average of total salary of all dept

SELECT AVG(salary) GROUP BY dept_name;

Then I lost. I did not find the way to proceed.

I found that. But I am looking for more explanation as I cannot understand it from this link.

Thank you for help.

CodePudding user response：

Read your last question and found that you are using mysql5.8, then you can use the analysis function

select distinct t1.dept_name
  from (
select t1.*,
       sum(salary) over(partition by dept_name) val,
       sum(salary) over() / count(distinct dept_name) over() avg
  from Instructor t1
) t1
 where t1.val > t1.avg

CodePudding user response：

Here's a few ways to do it in a mySQL version that doesn't support CTEs (I'm assuming that's why they are having you rewrite the query to omit the with). Also, you are calculating the average salary by department, but the question is asking to find the average TOTAL salary of each department, then return the ones that fall above that.

https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=fefea829cff3e20aea93634f9a4114d6

With a subquery:

SELECT dept_name
FROM (
  SELECT dept_name, sum(salary) as salaries
  FROM instructor GROUP BY dept_name
) d

WHERE salaries > (
  SELECT avg(salaries) as avgSalaries FROM (
    SELECT dept_name, sum(salary) as salaries
    FROM instructor GROUP BY dept_name
  ) z
)

With a join:

SELECT dept_name
FROM (
  SELECT dept_name, sum(salary) as salaries
  FROM instructor GROUP BY dept_name
) d
CROSS JOIN (
  SELECT avg(salaries) as avgSalaries FROM (
    SELECT dept_name, sum(salary) as salaries
    FROM instructor GROUP BY dept_name
  ) z
) avgs
WHERE salaries > avgs.avgSalaries

With a session variable (this is deprecated in later versions, but does still work):

SELECT avg(salaries) INTO @avg FROM (
  SELECT dept_name, sum(salary) as salaries
  FROM instructor GROUP BY dept_name
) z;

SELECT @avg;

SELECT dept_name
FROM instructor GROUP BY dept_name
HAVING sum(salary) > @avg;

All good techniques to understand.