select temp.dept_no, dept_name, title, count(1) as count
from (
    select dept_no, title
    from dept_emp a join titles b on a.emp_no=b.emp_no
) temp join departments c on temp.dept_no=c.dept_no
group by temp.dept_no, dept_name, title
order by temp.dept_no, title