bugfix> postgresql > 投稿

PostgreSQLで次のクエリを使用しています。

SELECT
        pegawai_nama,
        COUNT(operasi_operator_dokter) AS total,
        CASE tindakan_golongan WHEN 'KECIL' THEN SUM(tariftindakan_biaya_alkes)
            ELSE 0
        END AS KECIL,
        CASE tindakan_golongan WHEN 'BESAR' THEN SUM(tariftindakan_biaya_alkes)
            ELSE 0
        END AS BESAR,
        CASE tindakan_golongan WHEN 'KHUSUS' THEN SUM(tariftindakan_biaya_alkes
            ELSE 0
        END AS SEDANG
    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, tindakan_golongan;

ただし、以下の結果は私が望んでいるものではありません:

代わりに、次の結果が必要です。

pegawai_nama            total    kecil     besar   sedang       khusus       
DR. JOKO TRIYONO, SPM   2        189000    0       909700       0                
DR. DJOHAR ANWAR        3        567000    0       0            0            

ありがとうございました

回答 3 件
  • GROUP BYから「tindakan_golongan」列を取り出してみてください。

  • これをネストされたクエリとして使用し、再度グループ化します。

    select pegawai_nama, sum(total) as total , sum(kecil) as kecil, sum(besar) as besar, sum(sedang) as sedang
    from (
    -- insert your query here
    ) sub
    group by pegawai_nama
    
    

  • Georgi Raychevに感謝します。あなたのコードはとても助かります。これが私の新しいコードです

    SELECT
            sub.pegawai_nama,
            sum(sub.total) as total,
            sum(sub.kecil) as kecil,
            sum(sub.besar) as besar,
            sum(sub.sedang) as sedang,
            sum(sub.khusus) as khusus
        FROM (
        SELECT
        pegawai_nama,
        COUNT(operasi_operator_dokter) AS total,
            CASE tindakan_golongan WHEN 'KECIL' THEN SUM(tariftindakan_biaya_alkes +
                                                                                                        tariftindakan_biaya_bhp +
                                                                                                        tariftindakan_biaya_min +
                                                                                                        tariftindakan_biaya_max +
                                                                                                        tariftindakan_biaya_medik +
                                                                                                        tariftindakan_biaya_pelayanan +
                                                                                                        tariftindakan_biaya_sarana +
                                                                                                        tariftindakan_biaya_cover +tariftindakan_biaya)
                ELSE 0
            END AS kecil,
            CASE tindakan_golongan WHEN 'BESAR' THEN SUM(tariftindakan_biaya_alkes +
                                                                                                        tariftindakan_biaya_bhp +
                                                                                                        tariftindakan_biaya_min +
                                                                                                        tariftindakan_biaya_max +
                                                                                                        tariftindakan_biaya_medik +
                                                                                                        tariftindakan_biaya_pelayanan +
                                                                                                        tariftindakan_biaya_sarana +
                                                                                                        tariftindakan_biaya_cover +tariftindakan_biaya)
                ELSE 0
            END AS besar,
            CASE tindakan_golongan WHEN 'KHUSUS' THEN SUM(tariftindakan_biaya_alkes +
                                                                                                        tariftindakan_biaya_bhp +
                                                                                                        tariftindakan_biaya_min +
                                                                                                        tariftindakan_biaya_max +
                                                                                                        tariftindakan_biaya_medik +
                                                                                                        tariftindakan_biaya_pelayanan +
                                                                                                        tariftindakan_biaya_sarana +
                                                                                                        tariftindakan_biaya_cover +tariftindakan_biaya)
                ELSE 0
            END AS khusus,
            CASE tindakan_golongan WHEN 'SEDANG' THEN SUM(tariftindakan_biaya_alkes +
                                                                                                        tariftindakan_biaya_bhp +
                                                                                                        tariftindakan_biaya_min +
                                                                                                        tariftindakan_biaya_max +
                                                                                                        tariftindakan_biaya_medik +
                                                                                                        tariftindakan_biaya_pelayanan +
                                                                                                        tariftindakan_biaya_sarana +
                                                                                                        tariftindakan_biaya_cover +tariftindakan_biaya)
                ELSE 0
            END AS sedang
            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  
        GROUP BY pegawai_nama, tindakan_golongan
        ) AS sub
    
        GROUP BY sub.pegawai_nama
    
    

    ;

    これが結果です 新しい結果

あなたの答え