Total COUNT/SUM(COUNT) beserta COUNT dalam satu tampilan Query MySQL untuk menghitung prosentase

Setelah sekian lama tidak update dikarenakan banyak hal, kali ini saya akan membagikan pengalaman mengenai penggunaan operasi COUNT dan TOTAL COUNT dalam satu tampilan query.Dari beberapa referensi yang saya dapatkan di internet, sangat sedikit yang melibatkan penjumlahan dari hasil COUNT untuk ditampilkan didalam tabel hasil Query, sebagai contoh untuk menghitung proporsi hasil COUNT per-item terhadap TOTAL COUNT. Masih bingung maksudnya?

Kasus yang saya gunakan adalah saya memiliki beberapa data nama sales yang menyetor ke beberapa toko dengan menyetor beberapa jenis buah. Saya ingin mengetahui berapa frekuensi dari masing masing sales dan proporsi masing-masing sales terhadap total sales. Ok langsung saja ini adalah tabel yang sudah saya buat

tabeltoko

Saya ingin membuat laporan dengan tampilan seperti ini

hasil

Untuk menampilkan dua kolom pertama, yaitu nama_sales dan frekuensi_sales kita bisa menggunakan perintah COUNT dan dibantu perintah GROUP

SELECT nama_sales, COUNT(nama_sales) AS frekuensi_sales
FROM namatabel
GROUP BY nama_sales

Untuk menampilkan kolom ketiga diperoleh dari frekuensi_sales dibagi total frekuensi sales. Artinya kita haruslah menjumlahkan/SUM hasil dari perintah COUNT dengan tetap mempertahankan grouping nama_sales. Blok querynya mirip yang pertama yaitu

SELECT nama_sales, COUNT(nama_sales), [query] AS Prosentase
FROM namatabel
GROUP BY nama_sales

Sekarang tinggal menyelesaikan blok [query]-nya. Kita akan menggunakan perintah untuk menghitung semua frekuensi, artinya tidak menggunakan SUM, tetapi kita melakukan pendekatan dengan menghitung total semua frekuensi. Query-nya adalah

SELECT COUNT(*) FROM namatabel

Query ini akan menghasilkan output 10

Langkah berikutnya untuk rumus menghitung prosentase adalah (frekuensi/total frekuensi)*100. Frekuensi per sales kita ambil dari

COUNT(nama_sales)

sehingga query-nya menjadi

(COUNT(nama_sales) / SELECT COUNT(*) FROM namatabel)*100

Apabila inging hasilnya dibulatkan tanpa angka dibelakang koma, kita bisa menggunakan ROUND( ) sehingga query-nya menjadi

ROUND((COUNT(nama_sales)/(SELECT COUNT(*) FROM toko))*100, 0)

kalo kita menginginkan ada 2 angka setelah koma tinggal diganti saja menjadi

ROUND((COUNT(nama_sales)/(SELECT COUNT(*) FROM toko))*100, 2)

Tahapan terakhir adalah menggabungkan Query terakhir kita dengan Query pertama dengan mengganti blok [query]-nya sehingga menjadi

SELECT nama_sales, COUNT(nama_sales) AS frekuensi_sales,
ROUND((COUNT(nama_sales)/(SELECT COUNT(*) FROM toko))*100,0) AS Prosentase
FROM namatabel
GROUP BY nama_sales

Demikian query yang bisa saya bagikan, anda bisa memodifikasi dengan kalkulasi perhitungan yang lain. Semoga bermanfaat.

JOIN multiple table dengan operasi SUM beberapa kolom didalamnya – 2

Berawal dari tulisan saya yang berjudul “JOIN multiple table dengan operasi SUM beberapa kolom didalamnya”, ada pembaca yang menanyakan suatu kasus dengan prinsip yang hampir sama. Kasusnya adalah ingin membuat rekap data karyawan berupa total absensi shift, total lembur, total sakit, total cuti, total ijin. Ilustrasinya laporannya adalah sbb :

nama karyawan | total absen | total lembur | total sakit | total cuti | total ijin |
——————–|—————|—————-|————–|————|————|
karyawan A
karyawan B
dst

Ok sekarang akan saya kupas satu persatu apa yang akan diperlukan untuk membuat laporan tersebut. Harus diingat bahwa tidak setiap karyawan akan terisi semua rekapnya. Misal karyawan A mungkin dalam periode tertentu belum pernah cuti, artinya akan ada data kosong didalam rekapnya. Dengan melihat pola tersebut maka kolom yang cocok untuk dijadikan kunci-nya adalah kolom nama karyawan dan yang bisa menyelesaikan permasalahan tersebut dengan operasi LEFT JOIN dengan tabel nama karyawan sebagai tabel left yang akan dijoinkan dengan tabel yang lain yaitu tabel absen, tabel lembur, tabel sakit, tabel cuti dan tabel ijin. Itu artinya saya akan melakukan LEFT JOIN terhadap total 6 tabel!!! Fiuuhhh…….(untuk dasar-dasar penggunaan LEFT JOIN silahkan lihat tulisan saya yang pertama)

Ok kita mulai….

1. Tabel karyawan : tbl_karyawan

19-04-2014 21-18-16

2. Table lembur : tbl_absen_lembur

19-04-2014 21-21-40

3. Tabel absensi : tbl_absen_shift

19-04-2014 21-23-33

4. Tabel cuti : tbl_cuti

19-04-2014 21-24-38

5. Tabel sakit : tbl_sakit

19-04-2014 21-25-41

6. Tabel ijin : tbl_ijin

19-04-2014 21-26-49

Ok 6 tabel sudah dibuat, kemudian diisi dengan data. Dan tibalah kita pada pembuatan rekap laporan. Rencananya rekap ini bisa dipanggil berdasar periode tertentu. Perintah SQL-nya adalah sbb :

SELECT tbl_karyawan.id_karyawan, tbl_karyawan.nama_karyawan,
SUM(DATEDIFF(tbl_sakit.tgl_selesai,tbl_sakit.tgl_mulaisakit)) AS total_sakit, total_ijin, total_cuti,
total_absen, total_lembur
FROM tbl_karyawan
LEFT JOIN tbl_sakit ON tbl_karyawan.id_karyawan=tbl_sakit.id_karyawan
AND month(tbl_sakit.tgl_mulaisakit)='01' AND year(tbl_sakit.tgl_mulaisakit)='2014'


LEFT JOIN
(SELECT tbl_karyawan.id_karyawan,
SUM(DATEDIFF(tbl_ijin.tgl_selesai,tbl_ijin.tgl_mulaiijin)) AS total_ijin
FROM tbl_karyawan, tbl_ijin
WHERE tbl_karyawan.id_karyawan=tbl_ijin.id_karyawan
GROUP BY tbl_ijin.id_karyawan) AS ijin
ON tbl_karyawan.id_karyawan=ijin.id_karyawan

LEFT JOIN
(SELECT tbl_karyawan.id_karyawan,
SUM(DATEDIFF(tbl_cuti.tgl_selesai,tbl_cuti.tgl_mulaicuti)) AS total_cuti
FROM tbl_karyawan, tbl_cuti
WHERE tbl_karyawan.id_karyawan=tbl_cuti.id_karyawan
GROUP BY tbl_cuti.id_karyawan) AS cuti
ON tbl_karyawan.id_karyawan=cuti.id_karyawan

LEFT JOIN
(SELECT tbl_karyawan.id_karyawan,
SUM(HOUR(TIMEDIFF(tbl_absen_shift.waktu_selesai,tbl_absen_shift.waktu_mulaishift))) AS total_absen
FROM tbl_karyawan, tbl_absen_shift
WHERE tbl_karyawan.id_karyawan=tbl_absen_shift.id_karyawan
AND month(tbl_absen_shift.tgl_shift)='01' AND year(tbl_absen_shift.tgl_shift)='2014'
GROUP BY tbl_absen_shift.id_karyawan) AS absen
ON tbl_karyawan.id_karyawan=absen.id_karyawan


LEFT JOIN
(SELECT tbl_karyawan.id_karyawan,
SUM(HOUR(TIMEDIFF(tbl_absen_lembur.waktu_selesai,tbl_absen_lembur.waktu_mulailembur))) AS total_lembur
FROM tbl_karyawan, tbl_absen_lembur
WHERE tbl_karyawan.id_karyawan=tbl_absen_lembur.id_karyawan
AND month(tbl_absen_lembur.tgl)='01' AND year(tbl_absen_lembur.tgl)='2014'
GROUP BY tbl_absen_lembur.id_karyawan) AS lembur
ON tbl_karyawan.id_karyawan=lembur.id_karyawan

GROUP BY tbl_karyawan.id_karyawan ASC

Perhatikan saya menggunakan DATEDIFF untuk mencari rentang tanggal dan TIMEDIFF untuk mencari rentang waktu. Perhatikan pula huruf yang dicetak warna biru merupakan kunci dari setiap operasi join dengan rootnya.Yang dicetak warna hijau adalah nama kolom yang akan dimunculkan dan kaitannya dengan query join didalamnya. Sedangkan untuk huruf yang berwarna merah dapat anda ganti dengan pemanggilan dari PHP untuk report periodenya dengan metode $_GET[‘var’].

Apabila SQL diatas dieksekusi, maka data yang bisa saya dapatkan seperti dibawah ini

19-04-2014 21-31-33

Sebelum saya akhiri tulisan ini, untuk penggunaan hour(TIMEDIFF()) hanya akan menampilkan angka jam dengan pembulatan. Namun apabila kita ingin memodifikasi misalnya lama lembur adalah 2 jam 30 menit dengan format 2:30, maka perlu sedikit kita modifikasi. Logikanya jam kita ubah ke detik dicari totalnya dan dikembalikan ke jam. OK langsung saja untuk lebih jelasnya

SELECT TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF( waktu_selesai, waktu_mulailembur)))), "%h:%i")
FROM tbl_absen_lembur
WHERE month(waktu_mulailembur)='01'

’01’ adalah untuk bulan januari. Sesuaikan dengan kebutuhan, bisa dengan metode $_GET[‘var’] atau $_POST[‘var’]

OK saya rasa sekian dulu tulisan saya ini. Mudah-mudahan bermanfaat.

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 🙂