select t.dept_no,s.dept_name,t.title,t.count
from departments s
join
(SELECT a.dept_no,b.title,COUNT(B.TITLE) as count
FROM DEPT_EMP A
JOIN TITLES B ON A.EMP_NO = B.EMP_NO
where a.to_date = '9999-01-01'
GROUP BY a.dept_no,B.TITLE ) t on s.dept_no = t.dept_no
order by t.dept_no asc