Pada kesempatan ini kita akan membahas mengenai cara untuk menghitung total dan subtotal pada MySQL,  setidaknya ada tiga cara yang dapat kita lakukan, yaitu: Show
Catatan: Artikel ini cukup panjang, jika ingin langsung ke topik yang diinginkan, silakan klik salah satu link diatas. Artikel lain untuk menjumlah kolom MySQL: Menghitung Field/Kolom Pada Tabel MySQL Dengan Kondisi Tertentu Menggunakan COUNT IF Sebelum lebih lanjut membahas ketiga cara diatas, sobat bisa langsung praktek dengan mengunduh file sql yang digunakan dalam artikel ini. file ini akan membuat tabel sales yang berisi sekitar 400 data penjualan dengan layout seperti pada tabel berikut: +--------------+-----------+------------+---------+---------+---------+---------+--------------+ | id_transaksi | id_produk | tgl_byr | thn_byr | jml_byr | jns_byr | id_bank | id_pelanggan | +--------------+-----------+------------+---------+---------+---------+---------+--------------+ | 1 | 100 | 2016-09-20 | 2016 | 265000 | 1 | 16 | 1 | | 2 | 100 | 2016-10-11 | 2016 | 270000 | 1 | 24 | 2 | | 3 | 101 | 2016-08-17 | 2016 | 250000 | 1 | 22 | 2 | | 9 | 101 | 2016-08-24 | 2016 | 380000 | 1 | 16 | 2 | | 11 | 101 | 2016-05-10 | 2016 | 250000 | 1 | 1 | 1 | +--------------+-----------+------------+---------+---------+---------+---------+--------------+ I. Menghitung Total dan Subtotal Pada MySQL dengan PHPMungkin cara inilah yang sering kita gunakan, karena praktis dan mudah untuk di praktekkan, cara tersebut kurang lebih seperti ini:
output yang dihasilkan:
walaupun mudah dilakukan, namun terdapat beberapa kelemahan, yaitu:
Maka script PHP yang telah kita buat tapi perlu kita ubah menjadi:
Download File PHP Perhatikan baris ke 7, pada baris tersebut kita simpan semua hasil query ke dalam variabel Jika row yang dihasilkan sedikit, penyimpanan ke dalam variabel Mempertimbangkan berbagai kekurangan diatas, saya pribadi berpendapat sebaiknya cara ini digunakan sebagai alternatif terakhir  ketika memang tidak ada cara lain yang bisa digunakan, misal pada pivot table yang kompleks yang melibatkan banyak fungsi agregasi. II. Menggunakan WITH ROLLUP Pada Query MySQLPada MySQL, klausa Kelebihan klausa ini adalah simpel dan dapat menghasilkan total dan subtotal hanya dalam satu query, sehingga membuat pekerjaan kita jauh lebih mudah dan sederhana. Misal, untuk menyelesaikan pembuatan total dan subtotal sebagaimana contoh sebelumnya, kita hanya perlu menambahkan
itu saja cukup? ya itu saja, simpel kan? ….. mari kita lanjutkan, ketika kita jalankan query tersebut (menggunakan HEIDI SQL), hasil yang kita peroleh adalah: jika kita perhatikan, MySQL melakukan tiga fungsi penjumlahan berdasarkan kolom yang ada di
Perhatikan kolom yang diberikan tanda kotak merah, kenapa nilainya seperti itu? Nilai pada kolom tersebut, dan kolom sejenis yang dihasilkan oleh klausa GROUP BY dipilih secara bebas oleh MySQL, namun biasanya mengikuti nilai pada baris sebelumnya. 1 Mengganti Nilai NullJika kita perhatikan, nilai NULL kurang bermakna, sehingga kita perlu untuk mengubahnya. Kita dapat mengubah NULL dengan nilai lain menggunakan fungsi Catatan: di MSSQL kita harus mengganti Dengan menggunakanÂÂ
output yang dihasilkan: +---------+--------------+-----------+-----------+ | thn_byr | id_pelanggan | id_produk | jml_byr | +---------+--------------+-----------+-----------+ | 2015 | 1 | 100 | 13660000 | | 2015 | 1 | 101 | 19885000 | | 2015 | 1 | SUB TOTAL | 33545000 | | 2015 | 2 | 100 | 15145000 | | 2015 | 2 | 101 | 19595000 | | 2015 | 2 | SUB TOTAL | 34740000 | | 2015 | SUB TOTAL | SUB TOTAL | 68285000 | | 2016 | 1 | 100 | 15225000 | | 2016 | 1 | 101 | 18340000 | | 2016 | 1 | SUB TOTAL | 33565000 | | 2016 | 2 | 100 | 10710000 | | 2016 | 2 | 101 | 21700000 | | 2016 | 2 | SUB TOTAL | 32410000 | | 2016 | SUB TOTAL | SUB TOTAL | 65975000 | | TOTAL | SUB TOTAL | SUB TOTAL | 134260000 | +---------+--------------+-----------+-----------+ Jika ingin menampilkan tabel diatas pada browser, script PHP yang kita perlukan:
Download File PHP Script Diatas 2 Menggunakan ORDER BY Pada WITH ROLLUP Pada MySQLUntuk mengurutkan hasil query yang mengandung klausa Dengan Implisit OrderKetika kita menjalankan klausa GROUP BY, sebenarnya, disaat yang sama, MySQL juga melakukan pengurutan data,  namun karena tidak dinyatakan dengan jelas, maka sering disebut implisit order. Pada contoh sebelumnya terlihat bahwa kolom Pada implisit order, kita
dapat mengubah cara pengurutannya dengan menambahkan
Hasil yang kita peroleh: +---------+--------------+-----------+-----------+ | thn_byr | id_pelanggan | id_produk | jml_byr | +---------+--------------+-----------+-----------+ | 2016 | 2 | 101 | 21700000 | | 2016 | 2 | 100 | 10710000 | | 2016 | 2 | SUB TOTAL | 32410000 | | 2016 | 1 | 101 | 18340000 | | 2016 | 1 | 100 | 15225000 | | 2016 | 1 | SUB TOTAL | 33565000 | | 2016 | SUB TOTAL | SUB TOTAL | 65975000 | | 2015 | 2 | 101 | 19595000 | | 2015 | 2 | 100 | 15145000 | | 2015 | 2 | SUB TOTAL | 34740000 | | 2015 | 1 | 101 | 19885000 | | 2015 | 1 | 100 | 13660000 | | 2015 | 1 | SUB TOTAL | 33545000 | | 2015 | SUB TOTAL | SUB TOTAL | 68285000 | | TOTAL | SUB TOTAL | SUB TOTAL | 134260000 | +---------+--------------+-----------+-----------+ Pada tabel diatas terlihat bahwa kolom tahun kolom, Namun demikian, pada MySQL versi 5.7, fitur ini sudah
deprecated artinya sudah tidak disarankan lagi untuk digunakan karena pada versi berikutnya fitur ini akan dihilangkan. Untuk itu, disarankan untuk menggunakan eksplisit order dengan menggunakan klausa Dengan Eksplisit OrderUntuk dapat mengaplikasikan eksplisit order, terlebih dahulu kita buat temporary table (sub query) yang mengandung klausa Meneruskan contoh sebelumnya, kali ini kita urutkan kolom
Output yang dihasilkan adalah: +---------+--------------+-----------+-----------+ | thn_byr | id_pelanggan | id_produk | jml_byr | +---------+--------------+-----------+-----------+ | 2016 | 1 | 100 | 15225000 | | 2016 | 1 | 101 | 18340000 | | 2016 | 1 | SUB TOTAL | 33565000 | | 2016 | 2 | 100 | 10710000 | | 2016 | 2 | 101 | 21700000 | | 2016 | 2 | SUB TOTAL | 32410000 | | 2016 | SUB TOTAL | SUB TOTAL | 65975000 | | 2015 | 1 | 100 | 13660000 | | 2015 | 1 | 101 | 19885000 | | 2015 | 1 | SUB TOTAL | 33545000 | | 2015 | 2 | 100 | 15145000 | | 2015 | 2 | 101 | 19595000 | | 2015 | 2 | SUB TOTAL | 34740000 | | 2015 | SUB TOTAL | SUB TOTAL | 68285000 | | 0 | SUB TOTAL | SUB TOTAL | 134260000 | +---------+--------------+-----------+-----------+ Perhatikan bahwa pada baris ke 4, kita menggunakan perintah Kita sengaja gunakan angka 3 Menggunakan LIMIT Pada WITH ROLLUPPenggunaan klausaÂÂ
Output yang dihasilkan adalah: +---------+--------------+-----------+----------+ | thn_byr | id_pelanggan | id_produk | jml_byr | +---------+--------------+-----------+----------+ | 2015 | 1 | 100 | 13660000 | | 2015 | 1 | 101 | 19885000 | | 2015 | 1 | SUB TOTAL | 33545000 | | 2015 | 2 | 100 | 15145000 | | 2015 | 2 | 101 | 19595000 | +---------+--------------+-----------+----------+ Pada contoh diatas, terlihat bahwa MySQL mengambil 5 baris pertama hasil query termasuk baris yang dihasilkan oleh klausa WITH ROLLUP III. Menggunakan Query Tersendiri Untuk Menghitung Total Pada MySQLMenggunakan Dengan cara ini kita menambahkan baris baru secara manual untuk menghitung total dan subtotal. Penambahan tersebut bisa langsung disertakan pada query utama atau pada sub query, tergantung kondisi yang ada. Untuk menghitung total saja, kita cukup menambahkan query di bawah query utama, contoh:
Hasil yang kita peroleh: +---------+--------------+-----------+-----------+ | thn_byr | id_pelanggan | id_produk | jml_byr | +---------+--------------+-----------+-----------+ | 2016 | 2 | 101 | 21700000 | | 2016 | 1 | 101 | 18340000 | | 2016 | 1 | 100 | 15225000 | | 2016 | 2 | 100 | 10710000 | | 2015 | 1 | 101 | 19885000 | | 2015 | 2 | 101 | 19595000 | | 2015 | 2 | 100 | 15145000 | | 2015 | 1 | 100 | 13660000 | | TOTAL | NULL | NULL | 134260000 | +---------+--------------+-----------+-----------+ Perhatikan bahwa dengan cara ketiga ini, kita dapat lebih mudah melakukan pengolahan data. Pada contoh diatas, kita dapat mengurutkan data pembayaran mulai dari yang terbesar hingga terkecil dan sekaligus mengurutkan tahun dari yang terbesar hingga terkecil dengan hasil yang rapi, hal tersebut cukup sulit dilakukan jika menggunakan WITH ROLLUP. 1 Menggunakan Berbagai Fungsi AgregasiPenggunaan query secara manual juga memungkinkan kita untuk melakukan berbagai fungsi agregasi. Contoh berikut ini sedikit memodifikasi contoh sebelumya, kali ini kita akan:
Kode SQLnya adalah:
output yang dihasilkan adalah: +---------+--------------+-----------+-----------+--------+ | thn_byr | id_pelanggan | id_produk | jml_byr | rasio | +---------+--------------+-----------+-----------+--------+ | 2016 | 1 | 101 | 18340000 | 13.66 | | 2016 | 1 | 100 | 15225000 | 11.34 | | 2016 | 1-SUB TOTAL | 100 | 33565000 | 25.00 | | 2016 | 2 | 101 | 21700000 | 16.16 | | 2016 | 2 | 100 | 10710000 | 7.98 | | 2016 | 2-SUB TOTAL | 100 | 32410000 | 24.14 | | 2016 | TOTAL 2016 | 100 | 65975000 | 49.14 | | 2015 | 1 | 101 | 19885000 | 14.81 | | 2015 | 1 | 100 | 13660000 | 10.17 | | 2015 | 1-SUB TOTAL | 100 | 33545000 | 24.99 | | 2015 | 2 | 101 | 19595000 | 14.59 | | 2015 | 2 | 100 | 15145000 | 11.28 | | 2015 | 2-SUB TOTAL | 100 | 34740000 | 25.88 | | 2015 | TOTAL 2015 | 100 | 68285000 | 50.86 | | NULL | GRAND TOTAL | NULL | 134260000 | 100.00 | +---------+--------------+-----------+-----------+--------+ Perhatikan bahwa kita menyimpan nilai grand total (nilai yang kita gunakan untuk menghitung
rasio) ke dalam variabel Pada contoh diatas, kita kumpulkan kata-kata 1-SUB TOTAL, 2-SUB TOTAL, TOTAL 2016, TOTAL 2015 dan GRAND TOTAL pada kolom Bentuk kata-kata tersebut diatur sedemikian rupa sehingga ketika diurutkan, output yang dihasilkan sesuai dengan yang kita inginkan, contoh untuk tahun 2016, 1-SUB TOTAL lebih besar dari 1, sehingga di tempatkan di bawah 1, demikian juga 2 dan TOTAL 2016. 2 Penambahan Kolom BaruUntuk menghasilkan layout seperti diatas memang benar-benar tergantung pada pemilihan kata yang akan diurutkan, jika pemilihan katanya tidak tepat bisa jadi menghasilkan urutan yang berbeda. Misal pada perintah sql sebelumnya, baris ke 8 kita ganti perintahnya dari
dari tabel diatas terlihat bahwa urutannya berubah, tidak sesuai yang diharapkan. Bagaimana cara mengatasinya? Ada cara lain yaitu menambahkan kolom baru untuk
3 Membuat Temporary IndexKondisi menjadi sulit jika tidak ada urutan yang jelas antar row, query yang dilakukan membutuhkan logika yang cukup kompleks. Untuk mengatasi ini, kita perlu menciptakan temporary field berisi indeks (urutan) dari baris, melanjutkan contoh sebelumnya:
perintah sql nya adalah:
hasil yang kita dapatkan: +---------+--------------+-----------+----------+-------+------+---------------+ | thn_byr | id_pelanggan | id_produk | jml_byr | rasio | idx | idx_next_year | +---------+--------------+-----------+----------+-------+------+---------------+ | 2016 | 1 | 100 | 15225000 | 11.34 | 1 | 2-2016 | | 2016 | 1 | 101 | 18340000 | 13.66 | 2 | 3-2016 | | 2016 | 2 | 100 | 10710000 | 7.98 | 3 | 4-2016 | | 2016 | 2 | 101 | 21700000 | 16.16 | 4 | 5-2015 | | 2015 | 1 | 100 | 13660000 | 10.17 | 5 | 6-2015 | | 2015 | 1 | 101 | 19885000 | 14.81 | 6 | 7-2015 | | 2015 | 2 | 100 | 15145000 | 11.28 | 7 | 8-2015 | | 2015 | 2 | 101 | 19595000 | 14.59 | 8 | NULL | +---------+--------------+-----------+----------+-------+------+---------------+ Perhatikan bahwa kita menggunakan variabel @idx untuk menyimpan urutan baris utama dan @idx2 untuk menyimpan urutan baris berikutnya (nilai @idx2 adalah @idx + 1). Query Selanjutnya… Setelah kita ketahui nilai Jika nilainya tidak sama (seperti pada baris yang di bold), maka kita tahu bahwa baris tersebut adalah baris terakhir dari tahun berjalan, sehingga kita lakukan penjumlahan untuk mendapatkan subtotal tahun tersebut. Kita ubah querynya menjadi:
hasil yang kita peroleh: +---------+--------------+-----------+----------+-------+------+-----------+-----------+ | thn_byr | id_pelanggan | id_produk | jml_byr | rasio | idx | sub_total | total | +---------+--------------+-----------+----------+-------+------+-----------+-----------+ | 2016 | 1 | 100 | 15225000 | 11.34 | 1 | | | | 2016 | 1 | 101 | 18340000 | 13.66 | 2 | | | | 2016 | 2 | 100 | 10710000 | 7.98 | 3 | | | | 2016 | 2 | 101 | 21700000 | 16.16 | 4 | 65975000 | | | 2015 | 1 | 100 | 13660000 | 10.17 | 5 | | | | 2015 | 1 | 101 | 19885000 | 14.81 | 6 | | | | 2015 | 2 | 100 | 15145000 | 11.28 | 7 | | | | 2015 | 2 | 101 | 19595000 | 14.59 | 8 | 68285000 | 134260000 | +---------+--------------+-----------+----------+-------+------+-----------+-----------+ perhatikan perintah SQL pada baris ke 20, kita hitung subtotal untuk tahun yang sama ketika nilai pada variabel @idx1 dan @idx2 berbeda. Cara
diatas dapat menginspirasi kita untuk membuat subtotal per Cara terakhir ini lebih aman, setidaknya ketika kita ubah urutan tahunnya, nilai pada kolom sub_total akan mengikutinya. Coba pada perintah sql terakhir, kita ubah semua kata DESC menjadi ASC, maka data akan diurutkan berdasarkan nilai Namun demikian, terdapat beberapa kekurangan pada cara ini, diantaranya memerlukan query yang lebih kompleks, sehingga semakin besar jumlah data yang diolah, maka semakin lama waktu eksekusinya. VI. KesimpulanDari pembahasan diatas dapat disimpulkan bahwa untuk menghitung total dan subtotal pada mysql, setidaknya ada tiga cara yang dapat digunakan, yaitu:
dari ketiga cara diatas, mana yang sebaiknya kita gunakan? semua kembali kepada kondisi yang ada, saya pribadi penganut prinsip “sekali jadi”, dimana pengolahan data sebisa mungkin selesai diperintah SQL. Demikian pembahasan mengenai menghitung total dan subtotal pada MySQL, semoga bermanfaat. Subscibe NowSuka dengan artikel di Jagowebdev.com? jangan sampai ketinggalan, segera join ke milis kami untuk update informasi terbaru dari Jagowebdev.com |