select chospital.hoscode
,chospital.hosname
,COUNT(DISTINCT pd.pid) AS 'จำนวนคนตายมารับบริการคัดกรองNCD'
from ncd_person_ncd_screen
inner join person on ncd_person_ncd_screen.pid = person.pid
INNER JOIN persondeath pd ON ncd_person_ncd_screen.pid = pd.pid
LEFT JOIN chospital ON pd.pcucodeperson = chospital.hoscode
where age_year >= '15' and ncd_person_ncd_screen.screen_date between '2013-10-01' and '2014-09-30'
and person.typelive in ('0','1','3')
AND pd.deaddate <= ncd_person_ncd_screen.screen_date
GROUP BY chospital.hoscode;