select COALESCE(village.villno,'Total')villno,village.villname
,count(DISTINCT if(p.candobedhomesocial=1 and p.sex=1,p.pid,null))as 'ติดสังคม(ชาย)'
,count(DISTINCT if(p.candobedhomesocial=1 and p.sex=2,p.pid,null))as 'ติดสังคม(หญิง)'
,count(DISTINCT if(p.candobedhomesocial=2 and p.sex=1,p.pid,null))as 'ติดบ้าน(ชาย)'
,count(DISTINCT if(p.candobedhomesocial=2 and p.sex=2,p.pid,null))as 'ติดบ้าน(หญิง)'
,count(DISTINCT if(p.candobedhomesocial=3 and p.sex=1,p.pid,null))as 'ติดเตียง(ชาย)'
,count(DISTINCT if(p.candobedhomesocial=3 and p.sex=2,p.pid,null))as 'ติดเตียง(หญิง)'
,count(DISTINCT if(p.sex = 1,pc.pid,null))'รักษาโรคเรื้อรังต่อเนื่อง(ชาย)'
,count(DISTINCT if(p.sex = 2,pc.pid,null))'รักษาโรคเรื้อรังต่อเนื่อง(หญิง)'

FROM person p
INNER JOIN house ON p.hcode = house.hcode AND p.pcucodeperson = house.pcucode
INNER JOIN village ON house.villcode = village.villcode AND house.pcucode = village.pcucode
LEFT JOIN ctitle ON p.prename = ctitle.titlecode
left join (select pc.pcucodeperson,pc.pid,GROUP_CONCAT(chroniccode),pc.typedischart
from personchronic pc
where pc.typedischart ='03'
GROUP BY pc.pid
)pc on p.pcucodeperson = pc.pcucodeperson and p.pid=pc.pid
left join (select persondeath.pid,persondeath.pcucodeperson
from persondeath where deaddate <= '2015-06-01' or deaddate is null)pd on p.pid = pd.pid
WHERE YEAR(FROM_DAYS(DATEDIFF(CURDATE(),p.birth)))>=60
and right(village.villcode,2)!= '00'
and p.typelive in (0,1,3)
and p.dischargetype=9
and pd.pid is null
GROUP BY village.villno
with ROLLUP;