没用group by

select distinct de.dept_no , dep.dept_name , t.title , (select count(title) from titles t1 where 
                                              emp_no in 
(select emp_no from dept_emp d1 where d1.dept_no = de.dept_no)
                                             and t1.title =t.title ) 
from dept_emp de join departments dep using(dept_no)
join titles t using(emp_no) order by dept_no , title;

用了group by

select de.dept_no,de.dept_name, title ,count(t.title) as count from titles t
join dept_emp using(emp_no)
join departments de using(dept_no)
group by de.dept_no,t.title
order by dept_no