select drugname,visitdrug.drugcode,drugcode24,
    count(distinct visit.pcucode,visit.pid) as 'คน' ,
    count(visitdrug.unit) as 'ครั้ง',
    visitdrug.costprice as 'ราคาทุน',
      visitdrug.realprice as 'ราคาขาย',
    sum(visitdrug.unit)as 'จำนวนยาที่จ่ายไป',
    sum(visitdrug.unit *visitdrug.costprice) as 'ยอดต้นทุนรวม',
    sum(visitdrug.unit *visitdrug.realprice) as 'ยอดขายรวม'
 
from visit 
left join visitdrug on visit.visitno = visitdrug.visitno and visit.pcucode = visitdrug.pcucode
left join cdrug on visitdrug.drugcode = cdrug.drugcode
INNER JOIN visitdiag ON visit.pcucode=visitdiag.pcucode AND visit.visitno=visitdiag.visitno
where 
cdrug.antibio='1' and visit.visitdate between  '2014-04-01' and '2014-04-31'
AND visitdiag.diagcode like 'J0%' or 'J101' or 'J111' or 'J20%' or 'J21%' or 'B053' or 'H65%' or 'H66%' or 'H67%' or 'H72%' 
group by cdrug.drugcode;