JOIN multiple table dengan operasi SUM beberapa kolom didalamnya

Tulisan ini mengawali tahun 2011 dimana saya punya permasalahan untuk menampilkan hasil query dari beberapa tabel dimana pada beberapa tabel membutuhkan operasi SUM (penjumlahan). Dari beberapa kali surfing, kita bisa melakukan operasi JOIN pada beberapa tabel. Namun demikian akan muncul permasalahan apabila dari beberapa tabel tersebut dibutuhkan operasi penjumlahan. Hasil yang kita peroleh terhadap penjumlahan tersebut adalah salah, saya tidak bisa menjelaskan secara panjang kesalahan tersebut, namun untuk mengetahui mengapa silahkan dicoba query secara terpisah.

Ok langsung saja dengan contoh saya memiliki 3 buah tabel

1. Tabel data_obat. Tabel ini memuat data kode dan nama obat dengan contoh seperti ini

2. Tabel supplier_obat. Tabel ini memuat pengiriman obat oleh supplier

3. Tabel pakai_obat. Tabel ini digunakan untuk menampung data pemakaian obat

Sekarang kita ingin menampilkan data rekap pemakain seluruh obat dengan kolom : kode, nama_obat, stok, pemakaian
Stok diambil dari jumlah obat berdasar kode dari supplier, sedangkan pemakaian diambil dari jumlah pemakaian obat berdasar jenis obat. Kita akan menggunakan LEFT JOIN untuk menampilkan semua data obat terhadap tabel lain.

Menampilkan data stok

SELECT data_obat.kode, data_obat.nama_obat, SUM(supplier_obat.jumlah) AS stok
FROM data_obat LEFT JOIN supplier_obat
ON data_obat.kode=supplier_obat.kode
GROUP BY data_obat.kode ASC

Menampilkan data pemakaian

SELECT data_obat.kode, data_obat.nama_obat, SUM(pakai_obat.jml_pakai) AS pemakaian
FROM data_obat LEFT JOIN pakai_obat
ON data_obat.kode=pakai_obat.kode
GROUP BY data_obat.kode ASC

Kita akan menggunakan 2 operasi LEFT JOIN
SELECT data_obat.kode, data_obat.nama_obat, SUM(supplier_obat.jumlah) AS stok, SUM(pakai_obat.jml_pakai) AS pemakaian
FROM data_obat
LEFT JOIN supplier_obat ON data_obat.kode=supplier_obat.kode
LEFT JOIN pakai_obat ON pakai_obat.kode=data_obat.kode
GROUP BY data_obat.kode ASC

Ternyata data stok menunjukkan hasil yang salah bila kita bandingkan dengan query secara terpisah. Kenapa demikian? Hal ini disebabkan transaksi penggunaan obat lebih dari 1x maka stok akan dikalikan dengan berapa kali obat tersebut dipakai.

SOLUSI

SELECT data_obat.kode, data_obat.nama_obat, SUM(supplier_obat.jumlah) AS stok, pemakaian
FROM data_obat
LEFT JOIN supplier_obat ON data_obat.kode=supplier_obat.kode
LEFT JOIN
(SELECT data_obat.kode, SUM(pakai_obat.jml_pakai) AS pemakaian
FROM data_obat, pakai_obat
WHERE data_obat.kode=pakai_obat.kode
GROUP BY pakai_obat.kode ASC) AS pakai
ON data_obat.kode=pakai.kode
GROUP BY data_obat.kode ASC

Semoga membantu 🙂

Iklan