mysql分组查询的依据_mysql – sql查询帮助多个计数列和分组依据

我有下表学生：

id | status | school | name

----------------------------

0 | fail | skool1 | dan

1 | fail | skool1 | steve

2 | pass | skool2 | joe

3 | fail | skool2 | aaron

我想要一个给我的结果

school | fail | pass

---------------------

skool1 | 2 | 0

skool2 | 1 | 1

我有这个,但它很慢,

SELECT s.school, (

SELECT COUNT( * )

FROM school

WHERE name = s.name

AND status = 'fail'

) AS fail, (

SELECT COUNT( * )

FROM school

WHERE name = s.name

AND status = 'pass'

) AS pass,

FROM Students s

GROUP BY s.school

建议？