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

19 thoughts on “JOIN multiple table dengan operasi SUM beberapa kolom didalamnya

  1. mas aku mau nanyak.. misal aq mau ngitung jumlah hutang jaminan dengan join antara data penjamin sama daftar hutang semua penjamin, jadi hasilnya biar setiap penjamin hutangnya bisa dijumlah.. gmana querynya ?? bantu tolong.. buat TA mas..

    • Maaf lama tidak update blog……
      OK misalnya data pinjamian adalah sbb
      dpid | nama | alamat |
      ————————-
      1 |wawan |bantul |
      2 |bejo |sleman |
      3 |panjul|kulonprogo |
      4 |joko |kodya |

      kemudian data pinjaman misalnya
      dpjid | dpid | pinjaman |
      ————————–
      1 |1 |500000 |
      2 |1 |2000000 |
      3 |3 |750000 |
      4 |4 |1000000 |
      5 |3 |5000000 |
      6 |2 |1500000 |
      7 |4 |750000 |

      Hasil yang diinginkan adalah dibawah ini (mudah-mudahan ini yg dimaksud
      nama | tot_jaminan |
      ————————
      bejo |1500000 |
      joko |1750000 |
      panjul |5750000 |
      wawan |2500000 |

      maka perintah SQLnya adala sbb :
      SELECT nama, SUM(data_pinjaman.pinjaman) AS tot_jaminan FROM data_penjamin
      LEFT JOIN data_pinjaman
      ON data_penjamin.dpid=data_pinjaman.dpid
      GROUP BY data_penjamin.nama

      Semoga membantu

  2. mas kalo kaya tabel diatas gitu kan udah dicari stok sama pemakaiannya..nah terus kalo mau lanjutin satu kolom lagi namanya total dari penjumlahan stok sm pemakaiannya masing” obat jdi gmn tuh kelanjutan query sql nya.. mohon bantuannya mas πŸ™‚

    • Stok yang saya maksud disini adalah jumlah barang dari supplier sebelum pemakaian. Kalau yang dimaksud adalah berapa jumlah obat yang masih tersisa di gudang berdasar jenis obat, maka kita tinggal memodifikasi sedikit SQLnya. Logikanya sisa obat di gudang adalah jumlah dari supplier dikurangi pemakaian, maka SQLnya menjadi :
      SELECT data_obat.kode, data_obat.nama_obat, SUM(supplier_obat.jumlah) AS stok, pemakaian,
      SUM(supplier_obat.jumlah)-pemakaian AS sisa
      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

      atau kalau memang ingin menjumlah stok dengan pemakaian, ganti saja notasi – menjadi + pada SUM(supplier_obat.jumlah)-pemakaian AS sisa

      Semoga bisa membantu
      Terima kasih.

  3. Ping-balik: JOIN multiple table dengan operasi SUM beberapa kolom didalamnya – 2 | Ndholkondho's Weblog

  4. SELECT tbl_dapok.nama,tbl_dapok.pkt_corp,tbl_dapok.nrp,tb_sumber.id_s, sum(tb_sumber.nilai)AS SUMBER_DIK,penugasan FROM TBL_DAPOK

    LEFT JOIN tb_sumber on tbl_dapok.nrp = tb_sumber.tbl_dapok_nrp
    LEFT JOIN
    (SELECT tbl_dapok.nrp, SUM(tb_tipologi.point_penugasan) AS penugasan
    FROM TBL_DAPOK,tb_tipologi

    WHERE tbl_dapok.nrp=tb_tipologi.tbl_dapok_nrp
    GROUP BY tb_tipologi.tbl_dapok_nrp ASC) AS tugas ON tbl_dapok.nrp=tugas.nrp
    GROUP BY tbl_dapok.nrp ASC

    mas kalau mau muncul kan id dari tb_tipologi..agar bisa di hapus

    • Cara memasukkan id dari tb_tipologi adalah dengan menambahkan pada baris (SELECT … dan dibikin alias, kemudian alias tersebut dideclare pada baris pertama querynya. seperti contoh di artikel yg saya cetak tebal.
      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

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s