select group504code
,group504name
,sum(if((YEAR(visit.visitdate) = '2009' ),1,0)) as '2552'
,sum(if((YEAR(visit.visitdate) = '2010' ),1,0)) as '2553'
,sum(if((YEAR(visit.visitdate) = '2011' ),1,0)) as '2554'
,sum(if((YEAR(visit.visitdate) = '2012' ),1,0)) as '2555'
,sum(if((YEAR(visit.visitdate) = '2013' ),1,0)) as '2556'
from visit
inner join visitdiag on visit.visitno = visitdiag.visitno and visit.pcucode = visitdiag.pcucode
INNER JOIN cright ON cright.rightcode = visit.rightcode
INNER JOIN crightgroup on crightgroup.rightgroupcode = cright.rightgroup
inner join cdisease on visitdiag.diagcode = cdisease.diseasecode
inner join cdisease504 on cdisease.code504 = cdisease504.group504code and cdisease504.group504code <> '99'
where visit.visitdate between '2009-01-01' and CURDATE() and cright.rightgroup not in ( '1','2')
group by group504code,group504name;