select distinct departments.*,titles.title,count(titles.title) over (partition by departments.dept_no , titles.title) from departments left join dept_emp on dept_emp.dept_no=departments.dept_no left join titles on titles.emp_no=dept_emp.emp_no order by departments.dept_no,titles.title