mysql case when 统计某字段不同值的数量

SELECT
	COACH_NAME,
	sum(CASE WHEN STATE = 0 THEN 1 ELSE 0 END) AS '0',
	   sum (CASE WHEN STATE = 1 THEN 1 ELSE 0 END) AS '1',
	   SUM (CASE WHEN STATE = 9 THEN 1 ELSE 0 END) AS '9'  
FROM
	tt_f_schedule
WHERE
	tt_f_schedule.START_TIME >= '2016-09-01 00:00:00'
AND tt_f_schedule.END_TIME <= '2016-09-30 24:00:00'
GROUP BY
	COACH_NAME

查出所有教练状态分别为0,1,9的数据量

结果为

select COACH_NAME,count(COACH_NAME) as num from tt_f_schedule where STATE = 1 group by COACH_NAME;

分别查询不同教练 state=1的数据