Cara menggunakan query sum pada mysql

Pada kesempatan kali ini kita akan membahas cara menjumlahkan nilai pada field tertentu dengan kondisi tertentu menggunakan fungsi SUM dan IF.

Fungsi SUM digunakan untuk menjumlahkan nilai pada row yang ada pada suatu kolom, sedangkan fungsi IF digunakan untuk menguji nilai pada row tersebut (berdasarkan satu kondisi tertentu) sehingga diperoleh nilai yang akan digunakan dalam proses penjumlahan.

Persiapan Data

Pada tutorial ini, kita akan menggunakan tiga tabel. Untuk memudahkan, kita akan membuat tiga tabel sederhana yaitu tabel penjualan, pelanggan, dan produk.  Skema tabel dan hubungan (relasi) antar tabel tampak seperti gambar berikut:

File SQL diatas dapat digunakan untuk membuat semua tabel yang diperlukan beserta contoh datanya.

Pada tutorial ini, kita akan mengolah data pada tabel penjualan, agar lebih mudah dipahami, berikut ini penjelasan field pada tabel penjualan:

• id_transaksi merupakan nilai unik dari tabel penjualan. Field ini merupakan primary key untuk tabel penjualan.
• id_pelanggan merupakan merupakan nomor id dari pelanggan. Field ini merupakan foreign key dari field id_pelanggan yang ada pada tabel pelanggan
• id_produk merupakan nomor id untuk produk. Field ini merupakan foreign key dari field id_produk yang ada pada tabel produk.
• tgl_byr merupakan tanggal pembayaran dengan format yyyy-mm-dd.
• jml_byr merupakan jumlah pembayaran.

Adapun isi dari tabel penjualan seperti tampak pada tabel berikut:

+--------------+--------------+-----------+------------+---------+
| id_transaksi | id_pelanggan | id_produk | tgl_byr    | jml_byr |
+--------------+--------------+-----------+------------+---------+
|            1 |            1 | 111       | 2016-02-02 | 1500000 |
|            2 |            1 | 112       | 2016-03-10 | 1250000 |
|            3 |            1 | 113       | 2016-04-10 |  750000 |
|            4 |            2 | 112       | 2015-02-02 |  500000 |
|            5 |            2 | 112       | 2015-03-10 | 3500000 |
|            6 |            2 | 113       | 2016-04-10 | 1500000 |
|            7 |            3 | 115       | 2016-02-02 | 1550000 |
|            8 |            3 | 115       | 2015-03-10 | 2730000 |
|            9 |            3 | 111       | 2016-04-10 | 4949000 |
|           10 |            1 | 115       | 2015-02-02 | 2450000 |
|           11 |            1 | 111       | 2016-03-10 | 1725000 |
|           12 |            4 | 111       | 2015-04-10 |  775000 |
|           13 |            4 | 115       | 2016-04-10 | 2900000 |
+--------------+--------------+-----------+------------+---------+

Sedangkan untuk tabel pelanggan, data yang akan kita gunakan adalah sebagai berikut:

+--------------+---------+-----------+---------+
| id_pelanggan | nama    | alamat    | id_staf |
+--------------+---------+-----------+---------+
|            1 | Alfa    | Jakarta   |       1 |
|            2 | Beta    | Semarang  |       1 |
|            3 | Charlie | Surabaya  |       2 |
|            4 | Delta   | Surakarta |       3 |
+--------------+---------+-----------+---------+

Penggunaan Fungsi SUM IF

Selanjutnya, mari kita gunakan fungsi SUM dan IF untuk melakukan penjumlahan semua penjualan berdasarkan tahun, query yang kita gunakan adalah:

SELECT  SUM(IF( YEAR(tgl_byr) = 2016, jml_byr, 0)) AS jml_2016,
        SUM(IF( YEAR(tgl_byr) = 2015, jml_byr, 0)) AS jml_2015
FROM penjualan

Hasil yang kita peroleh:

+----------+----------+
| jml_2016 | jml_2015 |
+----------+----------+
| 16124000 |  9955000 |
+----------+----------+

Pada query diatas, MySQL akan melakukan pengecekan pada tiap-tiap row satu per satu. MySQL akan menjalankan fungsi YEAR untuk mendapatkan tahun dari data tanggal ada pada kolom tgl_byr, selanjutnya jika:

• Hasil fungsi YEAR adalah 2016, maka, ambil nilai pada kolom jml_byr untuk disertakan dalam proses penjumlahan, jika tidak, gunakan nilai nol ( 0 ).
• Proses diatas berlaku juga untuk tahun 2015.

Penting diperhatikan bahwa antara SUM dengan tanda kurung buka tidak boleh ada spasi seperti: SUM ( jika tidak, maka akan muncul pesan error: SQL Error (1630): FUNCTION tutorial_sum_if.SUM does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

Selanjutnya, jika kita ingin mengetahui jumlah pembelian per pelanggan per tahun, maka kita dapat mengelompokkan data berdasarkan kolom id_pelanggan, query yang kita gunakan:

SELECT  id_pelanggan,
        SUM(IF( YEAR(tgl_byr) = 2016, jml_byr, 0)) AS jml_2016,
        SUM(IF( YEAR(tgl_byr) = 2015, jml_byr, 0)) AS jml_2015,
        SUM(jml_byr) AS TOTAL
FROM penjualan
GROUP BY id_pelanggan

Hasil yang kita dapatkan:

+--------------+----------+----------+---------+
| id_pelanggan | jml_2016 | jml_2015 | TOTAL   |
+--------------+----------+----------+---------+
|            1 |  5225000 |  2450000 | 7675000 |
|            2 |  1500000 |  4000000 | 5500000 |
|            3 |  6499000 |  2730000 | 9229000 |
|            4 |  2900000 |   775000 | 3675000 |
+--------------+----------+----------+---------+

Terakhir, mari kita tampilkan informasi nama pelanggan dengan cara menggabungkan tabel penjualan dan tabel pelanggan. Adapun query yang kita perlukan:

SELECT  id_pelanggan,
	nama,
        SUM(IF( YEAR(tgl_byr) = 2016, jml_byr, 0)) AS jml_2016,
        SUM(IF( YEAR(tgl_byr) = 2015, jml_byr, 0)) AS jml_2015,
        SUM(jml_byr) AS TOTAL
FROM penjualan
LEFT JOIN pelanggan USING(id_pelanggan)
GROUP BY id_pelanggan

Hasil yang kita peroleh:

+--------------+---------+----------+----------+---------+
| id_pelanggan | nama    | jml_2016 | jml_2015 | TOTAL   |
+--------------+---------+----------+----------+---------+
|            1 | Alfa    |  5225000 |  2450000 | 7675000 |
|            2 | Beta    |  1500000 |  4000000 | 5500000 |
|            3 | Charlie |  6499000 |  2730000 | 9229000 |
|            4 | Delta   |  2900000 |   775000 | 3675000 |
+--------------+---------+----------+----------+---------+

Fungsi IF hanya dapat digunakan untuk melakukan pengujian satu kondisi sehingga jika kita ingin menjumlahkan kolom dengan beberapa kondisi, kita tidak bisa menggunakan fungsi IF, melainkan menggunakan CASE.

Jika kita ingin menghitung jumlah baris, misal untuk menghitung jumlah transaksi per pelanggan, kita dapat menggunaan fungsi COUNT. Pembahasan fungsi tersebut dapat diikuti pada tutorial: Menghitung Field (Kolom) Pada Tabel MySQL Dengan Kondisi Tertentu Menggunakan COUNT IF

UPDATE

Mas awy dalam feedbacknya ingin membuat grand total dari total yang ada, saya rasa perlu untuk saya tambahkan pada artikel ini sehingga dapat lebih informatif.

Terdapat beberapa cara untuk membuat grand total dari total yang ada, beberapa diantaranya dibahas pada artikel: Menghitung Total dan Subtotal Pada MySQL.

Pada contoh kali ini kita akan menghitung grand total menggunakan query tersendiri kemudian digabungkan dengan query utama menggunakan UNION ALL, querynya adalah sebagai berikut:

SELECT  id_pelanggan,
	nama,
        SUM(IF(YEAR(tgl_byr) = 2016, jml_byr, 0)) AS jml_2016,
        SUM(IF(YEAR(tgl_byr) = 2015, jml_byr, 0)) AS jml_2015,
        SUM(jml_byr) AS TOTAL
FROM penjualan
LEFT JOIN pelanggan USING(id_pelanggan)
GROUP BY id_pelanggan
UNION ALL
SELECT  "" AS id_pelanggan,
	"Grand Total" as nama,
        SUM(IF(YEAR(tgl_byr) = 2016, jml_byr, 0)) AS jml_2016,
        SUM(IF(YEAR(tgl_byr) = 2015, jml_byr, 0)) AS jml_2015,
        SUM(jml_byr) AS TOTAL
FROM penjualan

Hasil yang kita dapatkan:

+--------------+-------------+----------+----------+----------+
| id_pelanggan | nama        | jml_2016 | jml_2015 | TOTAL    |
+--------------+-------------+----------+----------+----------+
| 1            | Alfa        |  5225000 |  2450000 |  7675000 |
| 2            | Beta        |  1500000 |  4000000 |  5500000 |
| 3            | Charlie     |  6499000 |  2730000 |  9229000 |
| 4            | Delta       |  2900000 |   775000 |  3675000 |
|              | Grand Total | 16124000 |  9955000 | 26079000 |
+--------------+-------------+----------+----------+----------+

Pada query diatas, query pertama dan kedua bentuknya mirip, hanya bedanya, pada query pertama, data kita kelompokkan (GROUP BY) berdasarkan kolom id_pelanggan sedangkan yang kedua, kita tidak melakukan pengelompokan data, MySQL otomatis akan menggabungkan semua data untuk masing masing kolom.

Demikian pembahasan mengenai cara menggunakan fungsi SUM IF untuk menjumlahkan data dengan kriteria tertentu, semoga bermanfaat.

Subscibe Now

Suka dengan artikel di Jagowebdev.com? jangan sampai ketinggalan, segera join ke milis kami untuk update informasi terbaru dari Jagowebdev.com