sql - 我有可能从多个字段中计算记录

Question

查询的结果是这样的

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, d.jenis_penyakit, e.nama, c.icd_verif2,c.icd_verif3, d.icd_code
ORDER BY c.icd_verif ASC

我想要的是从 icd_code 到 icd_code3 的计数字段作为 jumlahpasien

标签： sqlpostgresql

Answer 1

我认为这就是您要实现的目标：

获取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, c.icd_verif2, c.icd_verif3, 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, d.jenis_penyakit, e.nama, c.icd_verif2, c.icd_verif3, d.icd_code
order by c.icd_verif asc