sql 语句sum if

SELECT room,COUNT(aid) num,sum(if(DATE_FORMAT(dzsj,'%d')='1',1,0)) num1,sum(if(DATE_FORMAT(dzsj,'%d')='2',1,0)) num2,sum(if(DATE_FORMAT(dzsj,'%d')='3',1,0)) num3,sum(if(DATE_FORMAT(dzsj,'%d')='4',1,0)) num4,sum(if(DATE_FORMAT(dzsj,'%d')='5',1,0)) num5,sum(if(DATE_FORMAT(dzsj,'%d')='6',1,0)) num6,sum(if(DATE_FORMAT(dzsj,'%d')='7',1,0)) num7,sum(if(DATE_FORMAT(dzsj,'%d')='8',1,0)) num8,sum(if(DATE_FORMAT(dzsj,'%d')='9',1,0)) num9,sum(if(DATE_FORMAT(dzsj,'%d')='10',1,0)) num10,sum(if(DATE_FORMAT(dzsj,'%d')='11',1,0)) num11,sum(if(DATE_FORMAT(dzsj,'%d')='12',1,0)) num12,sum(if(DATE_FORMAT(dzsj,'%d')='13',1,0)) num13,sum(if(DATE_FORMAT(dzsj,'%d')='14',1,0)) num14,sum(if(DATE_FORMAT(dzsj,'%d')='15',1,0)) num15,sum(if(DATE_FORMAT(dzsj,'%d')='16',1,0)) num16,sum(if(DATE_FORMAT(dzsj,'%d')='17',1,0)) num17,sum(if(DATE_FORMAT(dzsj,'%d')='18',1,0)) num18,sum(if(DATE_FORMAT(dzsj,'%d')='19',1,0)) num19,sum(if(DATE_FORMAT(dzsj,'%d')='20',1,0)) num20,sum(if(DATE_FORMAT(dzsj,'%d')='21',1,0)) num21,sum(if(DATE_FORMAT(dzsj,'%d')='22',1,0)) num22,sum(if(DATE_FORMAT(dzsj,'%d')='23',1,0)) num23,sum(if(DATE_FORMAT(dzsj,'%d')='24',1,0)) num24,sum(if(DATE_FORMAT(dzsj,'%d')='24',1,0)) num25,sum(if(DATE_FORMAT(dzsj,'%d')='26',1,0)) num26,sum(if(DATE_FORMAT(dzsj,'%d')='27',1,0)) num27,sum(if(DATE_FORMAT(dzsj,'%d')='28',1,0)) num28,sum(if(DATE_FORMAT(dzsj,'%d')='29',1,0)) num29,sum(if(DATE_FORMAT(dzsj,'%d')='30',1,0)) num30,sum(if(DATE_FORMAT(dzsj,'%d')='31',1,0)) num31 FROM `gh_appoint` WHERE state in (2,6) AND `dzsj` >= '2012-11-01' AND `dzsj` < adddate('2012-11-30',1) GROUP BY room ORDER BY `room` desc