SELECT det.dept_no,det.dept_name,det.title,COUNT(title) count FROM(SELECT de.dept_no,d.dept_name,t.title FROM dept_emp de INNER JOIN departments d ON de.dept_no=d.dept_no INNER JOIN titles t ON de.emp_no=t.emp_no ) det GROUP BY det.dept_no ,det.title order by det.dept_no ;