select n.dept_no,n.dept_name,title,count(title)
from departments n join dept_emp d using(dept_no)
join titles using(emp_no)
group by n.dept_no,title
order by n.dept_no,title

先部门分组，再title分组，即可