select drugname,visitdrug.drugcode,drugcode24,
    count(distinct visit.pcucode,visit.pid) as 'คน' ,
    count(visitdrug.unit) as 'ครั้ง',
    sum(visitdrug.unit)as 'จำนวน',
    visitdrug.costprice as 'ทุน',
    sum(visitdrug.unit *visitdrug.costprice) as 'ยอดเงินรวม'
 
from visit 
left join visitdrug on visit.visitno = visitdrug.visitno and visit.pcucode = visitdrug.pcucode
left join cdrug on visitdrug.drugcode = cdrug.drugcode
where 
cdrug.drugtype='01' and visit.visitdate between  '2014-10-01' and '2015-09-30'
group by cdrug.drugcode;
