SELECT
    DISTINCT
    T1.dept_no,
    T2.dept_name,
    T3.title,
    COUNT(1) OVER(PARTITION BY T1.dept_no,T3.title) AS COUNT
FROM
dept_emp         T1
LEFT JOIN
departments      T2
ON T1.dept_no = T2.dept_no
LEFT JOIN
titles           T3
ON T1.emp_no = T3.emp_no
ORDER BY 
    T1.dept_no,
    T3.title