SELECT
visitdiag.pcucode,
GROUP_CONCAT(visitdiag.diagcode) AS group_diagcode,
visit.visitno,
visit.pid,
count(*) AS 'จำนวนโรคที่วินิจฉัย',
GROUP_CONCAT(visit.symptoms) AS 'คำวินิจฉัย',
visit.weight,
visit.height,
visit.pressure,
visit.temperature,
visit.pulse,
visit.respri,
GROUP_CONCAT(visit.diagnote) AS group_diagnote,
visit.vitalcheck AS 'ผลการตรวจร่างกาย',
GROUP_CONCAT(DATE_FORMAT(visit.visitdate,'%m-%d-%Y')) AS group_visitdate,
visit.timeservice,
visit.timestart,
GROUP_CONCAT(cdrug.drugname) AS group_drugname,
visitdrug.unit,
visitdrug.dose
FROM
visitdiag
INNER JOIN visit ON visit.visitno = visitdiag.visitno
INNER JOIN visitdrug ON visit.visitno = visitdrug.visitno
INNER JOIN cdrug ON visitdrug.drugcode = cdrug.drugcode
WHERE
visit.visitdate < '2015-09-30' 
AND visit.diagnote IS NOT NULL
GROUP BY visit.visitno -- ,visit.visitdate,visit.pid -- visit.pid,visit.visitno,visitdiag.diagcode,visit.visitdate