PostgreSQL按医生分组汇总费用：查询结果不符合预期求修正

你的问题核心在于原查询的GROUP BY子句同时包含了pegawai_nama和tindakan_golongan，这会让数据库按「医生姓名+操作类别」的组合来分组，自然返回多行结果。要实现按医生单行汇总的需求，我们需要使用条件聚合——只按医生姓名分组，再针对每个操作类别单独计算费用总和。

修正后的查询语句（推荐PostgreSQL专属语法，简洁易读） #

SELECT 
    pegawai_nama,
    COUNT(*) AS total,
    SUM(tariftindakan_biaya_alkes) FILTER (WHERE tindakan_golongan = 'KECIL') AS KECIL,
    SUM(tariftindakan_biaya_alkes) FILTER (WHERE tindakan_golongan = 'BESAR') AS BESAR,
    SUM(tariftindakan_biaya_alkes) FILTER (WHERE tindakan_golongan = 'SEDANG') AS SEDANG,
    SUM(tariftindakan_biaya_alkes) FILTER (WHERE tindakan_golongan = 'KHUSUS') AS KHUSUS
FROM t_operasi
LEFT JOIN m_pasien ON t_operasi.operasi_pasien_norm = m_pasien.pasien_norm
LEFT JOIN t_pendaftaran ON t_operasi.operasi_pendaftaran_id = t_pendaftaran.pendaftaran_id
LEFT JOIN m_tindakan ON t_operasi.operasi_tindakan_id = m_tindakan.tindakan_id
LEFT JOIN m_tarif_tindakan ON m_tindakan.tindakan_id = m_tarif_tindakan.m_tindakan_id
LEFT JOIN m_pegawai ON cast(m_pegawai.pegawai_id as varchar(10)) = t_operasi.operasi_operator_dokter
LEFT JOIN t_diagnosa_pasien ON t_diagnosa_pasien.t_pendaftaran_id = t_pendaftaran.pendaftaran_id
LEFT JOIN m_icd ON m_icd.icd_id = t_diagnosa_pasien.m_icd_id
WHERE operasi_id IS NOT NULL 
  AND tindakan_golongan IN ('KECIL', 'BESAR', 'KHUSUS', 'SEDANG', '')
GROUP BY pegawai_nama;

兼容标准SQL的写法（适配其他数据库） #

如果需要兼容不支持FILTER语法的数据库，可以用CASE语句嵌套SUM实现：

SELECT 
    pegawai_nama,
    COUNT(*) AS total,
    SUM(CASE WHEN tindakan_golongan = 'KECIL' THEN tariftindakan_biaya_alkes ELSE 0 END) AS KECIL,
    SUM(CASE WHEN tindakan_golongan = 'BESAR' THEN tariftindakan_biaya_alkes ELSE 0 END) AS BESAR,
    SUM(CASE WHEN tindakan_golongan = 'SEDANG' THEN tariftindakan_biaya_alkes ELSE 0 END) AS SEDANG,
    SUM(CASE WHEN tindakan_golongan = 'KHUSUS' THEN tariftindakan_biaya_alkes ELSE 0 END) AS KHUSUS
FROM t_operasi
LEFT JOIN m_pasien ON t_operasi.operasi_pasien_norm = m_pasien.pasien_norm
LEFT JOIN t_pendaftaran ON t_operasi.operasi_pendaftaran_id = t_pendaftaran.pendaftaran_id
LEFT JOIN m_tindakan ON t_operasi.operasi_tindakan_id = m_tindakan.tindakan_id
LEFT JOIN m_tarif_tindakan ON m_tindakan.tindakan_id = m_tarif_tindakan.m_tindakan_id
LEFT JOIN m_pegawai ON cast(m_pegawai.pegawai_id as varchar(10)) = t_operasi.operasi_operator_dokter
LEFT JOIN t_diagnosa_pasien ON t_diagnosa_pasien.t_pendaftaran_id = t_pendaftaran.pendaftaran_id
LEFT JOIN m_icd ON m_icd.icd_id = t_diagnosa_pasien.m_icd_id
WHERE operasi_id IS NOT NULL 
  AND tindakan_golongan IN ('KECIL', 'BESAR', 'KHUSUS', 'SEDANG', '')
GROUP BY pegawai_nama;

关键修改点说明 #

1. 移除GROUP BY中的tindakan_golongan：现在仅按医生姓名分组，确保每个医生返回一行结果。
2. 条件聚合实现类别汇总：
   • PostgreSQL的FILTER语法可以精准筛选出对应类别的行计算总和，语法更直观；
   • 标准SQL的CASE写法则通过判断类别，对符合条件的行累加费用，不符合的返回0（如果想显示NULL可以去掉ELSE 0）。
3. 优化操作总数统计：因为WHERE子句已经过滤掉operasi_id IS NULL的行，用COUNT(*)代替COUNT(operasi_operator_dokter)更高效，结果完全一致。

预期输出示例 #

内容的提问来源于stack exchange，提问作者M. Syafi'i Asy'ari