select
  d.*,
  title,
  count(title) count
from
  (
    dept_emp de
    join titles t on de.emp_no = t.emp_no
  )
  left join departments d on de.dept_no = d.dept_no
  and de.to_date = '9999-01-01'
  and t.to_date = '9999-01-01'
group by
  d.dept_no,
  title
order by
  d.dept_no,
  title

耶耶耶我真棒！第一道困难的完完全全做对啦！！

总结一下：

聚合函数，也就是这里的count()是在，运行完group by之后，才进行运算的