I have the following 5 tables:
employees as e
dept_manager as dm
departments as d
titles as t
salaries as s
I would like to know how many male and female managers we have, along with the avg salary per gender within a department.
I wrote the query below but the numbers don't add up:
select
d.dept_name,
count(e.gender),
avg(salary)
from employees as e
Join dept_manager as dm on e.emp_no = dm.emp_no
Join departments as d on d.dept_no = dm.dept_no
Join titles as t on e.emp_no = t.emp_no
Join salaries as s on dm.emp_no = s.emp_no
where t.title ='manager'
group by d.dept_name, e.gender
order by d.dept_name ASC;
