SELECT
    D.dept_no,
    D.dept_name,
    T.title,
    COUNT(T.title) AS count
FROM
    dept_emp AS DE
    LEFT JOIN titles AS T ON T.emp_no = DE.emp_no
    LEFT JOIN departments AS D ON D.dept_no = DE.dept_no
GROUP BY
    D.dept_no,
    T.title
ORDER BY
    D.dept_no ASC,
    T.title ASC;