select C.dept_no,C.dept_name,D.title,count(D.title) as count
from 
(select A.dept_no,A.dept_name,B.emp_no
from departments A
left join dept_emp B
on A.dept_no = B.dept_no) as C
left join titles D
on C.emp_no = D.emp_no
group by C.dept_no,C.dept_name,D.title
order by dept_no ASC,title ASC