SUM IF mysql

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:

Download File SQL

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 |
+--------------+---------+----------+----------+---------+

Sumber : http://jagowebdev.com/menggunakan-sum-if-untuk-menjumlahkan-nilai-fieldkolom-suatu-tabel-pada-mysql/