查询结果是这样
SELECT d.icd_code,c.icd_verif2,c.icd_verif3,d.jenis_penyakit,COUNT(*) as totalapsien,e.nama as NamaPoly
FROM t_diagnosadanterapi c,icd d,m_poly e
WHERE c.status = '2' and c.icd_verif = d.icd_code and c.kdpoly = e.kode
GROUP BY c.icd_verif,c.kdpoly,e.nama,d.icd_code
ORDER BY c.icd_verif ASC
我想要的是将icd_code到icd_code3的计数字段作为jumlahpasien
我认为这是您要实现的目标：
使用链接到您的
icd_verify的值获取icd_code。
为了得到这个，我们必须：
1. left join icd to include those with no matching verif. 2. use coalesce to to check values from verif 1 to 3 3. Need to change the way you join the tables,its quiet an old style.
select d.icd_code,c.icd_verif2,c.icd_verif3,d.jenis_penyakit,count(1) as totalapsien,sum(case when coalesce(c.icd_verif1,0) != 0 then 1 else 0 end) as jumlahpasien,e.nama as NamaPoly
from t_diagnosadanterapi c
left join icd d on c.icd_verif = d.icd_code
inner join m_poly e on c.kdpoly = e.kode
where c.status = '2' and
group by c.icd_verif,c.kdpoly,e.nama,d.icd_code
order by c.icd_verif asc