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