SELECT classid , SUM(IF(gender ='男',1,0)) as '男', SUM(IF(gender ='女',1,0)) as '女' FROM student GROUP BY classid;