SELECT chospital.hoscode
,chospital.hosname
,COUNT(DISTINCT pd.pid) AS 'จำนวนผีที่แอบมารับบริการ'
FROM visit v
INNER JOIN persondeath pd ON v.pid = pd.pid AND v.pcucodeperson =pd.pcucodeperson
LEFT JOIN chospital ON pd.pcucodeperson = chospital.hoscode
WHERE v.visitdate BETWEEN '2014-10-01' AND '2015-09-30'
AND pd.deaddate <= v.visitdate
GROUP BY chospital.hoscode;