SELECT m.hmain hospcode,f4.hospmain, COUNT(1) visit,sum(f4.uc_money) as sumprice
FROM foreigner_opd_4 f4
LEFT JOIN co_mastercup m ON m.hsub = f4.hospcode
##### LEFT JOIN audit_details a ON a.hospcode=f4.hospcode AND a.vn=f4.vn ##### Query เฉพาะหน้าสรุป
WHERE f4.hospmain<>m.hmain
##### and (a.`status` ='0' OR a.`status` IS NULL) ##### Query เฉพาะหน้าสรุป
and f4.pdx not between 'u' and 'y'
and f4.pdx not between 'Z00' and 'Z008'
and f4.pdx not between 'Z02' and 'Z029'
and f4.pdx not between 'Z27' and 'Z279'
and f4.pdx not between 'Z30' and 'Z309'
and f4.pdx not between 'Z32' and 'Z349'
and f4.pdx not in('','Z012','Z014','Z111','Z123','Z124','Z133','Z251','Z232','Z240','Z241','Z244','Z245','Z246')
and f4.pdx not between 'c00' and 'c99'
and f4.pdx not between 'z510' and 'z514'
and f4.oldcode ='10'
and f4.citizenship='99'
and f4.uc_money>0
AND m.hmain in('10664','10834','10835','10836','10837','10838','10839','10840','10841','10842','10843','10844')
AND f4.hospcode in('10664','10834','10835','10836','10837','10838','10839','10840','10841','10842','10843','10844')
AND f4.vstdate BETWEEN '2022-01-01' AND '2022-01-31'
GROUP BY m.hmain, f4.hospmain