SELECT
visitdiag.diagcode,
cdisease.diseasenamethai,
Count(visitdiag.diagcode) AS Total
FROM visitdiag
LEFT JOIN visit ON visitdiag.pcucode = visit.pcucode AND visitdiag.visitno = visit.visitno
INNER JOIN cdisease ON cdisease.diseasecode = visitdiag.diagcode
WHERE visit.visitdate BETWEEN '2013-10-01' AND NOW()
AND visitdiag.diagcode LIKE 'Z%'
GROUP BY visitdiag.diagcode
ORDER BY Total DESC;