List the name of department that employ above-average women-CodePudding

I want to list the name of departments that employ above-average women.

In my code I have joined three tables employees, dept_emp and departments and selected dept_no and compared it to avg(dept_no) and in the end I grouped by department_name but it looks like I have stumbled on a problem and I can't find any solution can someone care to explain?

SELECT d.dept_name
FROM departments d
JOIN dept_emp de ON d.dept_no = de.dept_no
JOIN employees e ON de.emp_no = e.emp_no
WHERE dept_no > AVG(dept_no) AND e.gender = 'F';
GROUP BY d.dept_name;

This is how tables look:

The error I get in mysql workbench:

How out should look like (similar to this but like this)

CodePudding user response：

This might help. Join dept_emp and employees table GROUP BY dept_name use HAVING to count and in the end GROUP BY d.dept_no

SELECT dept_name, COUNT(e.gender) AS count FROM departments
JOIN dept_emp USING(dept_no)
JOIN employees USING(emp_no)
WHERE e.gender = 'F'
GROUP BY 1
HAVING count > (
SELECT AVG(a.rcount) FROM (
SELECT COUNT(e.gender) AS rcount FROM departments d
JOIN dept_emp USING(dept_no)
JOIN employees USING(emp_no)
WHERE e.gender = 'F'
GROUP BY  d.dept_no) a

CodePudding user response：

Refer fiddle here -

select t1.dname from 
(
select t.dname,t.cnt,avg(cnt) over (order by null) avg_cnt from
(
select ed.deptno,d.dname,count(*) cnt from emp_dept ed, emp e, dept d
where ed.empno = e.empno
and ed.deptno = d.deptno
and e.gender = 'F'
group by ed.deptno,d.dname
) t 
) t1
where t1.cnt > t1. avg_cnt