select t1.dept_no,departments.dept_name,t1.title,t1.cnt from 
(select dept_emp.dept_no,titles.title,count(*) cnt from dept_emp,titles where dept_emp.emp_no=titles.emp_no
group by dept_emp.dept_no,titles.title) t1,departments
where t1.dept_no=departments.dept_no 
order by t1.dept_no