Dalam mysql, penyimpanan dan pemrosesan data sering jadi masalah, termasuk dalam menangani field yang berisi “NULL” baik nilai tersebut berupa angka, baik itu integer, decimal, maupun double.
Ambil contoh ketika kita membuat database yang berisi data statistik yang rencananya diisi data yang besar, dimana ada nilai nol (0) dan kosong (NULL). nol akan dihitung, sedangkan NULL tidak.
Sekilas memang sama, seorang programmer database (databaser) yang perhatian sama efisiensi data, akan membuat field dengan tipe data “NULL” yang berarti hemat space, karena NULL tidak makan space
kemudian data yang memiliki nilai nol (0) dihitung, sedang yang NULL tidak
operasi matematis yang disediakan mysql cukup oke, sehingga olah, proses dan simpan data di mysql kemudian program interface nya digunakan untuk menampilkan hasilnya saja.
dalam sebuah operasi matematika di mysql, AVG/rata-rata akan menegasikan angka NULL,
Cth : rata-rata dari : 1,3,7,null,0,5,3 = (1+3+7+0+5+2)/6 = 3
nilai NULL tidak ikut dihitung bukan.. demikian juga dengan SUM, COUNT, STD_DEV, dsb
Coba dibuktikan dengan praktek dulu ya…
CREATE TABLE `data_contoh` (
`id` INT( 10 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`data_a` DOUBLE NULL ,
`data_b` DOUBLE NULL
) ENGINE = MYISAM ;
INSERT INTO `data_contoh` VALUES (1, 2, 6);
INSERT INTO `data_contoh` VALUES (2, 4, NULL);
INSERT INTO `data_contoh` VALUES (3, 5, NULL);
INSERT INTO `data_contoh` VALUES (4, NULL, 7);
dari tabel diatas kita mempunyai :
id = “1,2,3,4″
data_a = “2,4,5,null”
data_b = “6,NUL,NULL,7″
1. Kasus mencari rata-rata suatu kolom
SELECT AVG( data_a ) AS ratarata_a from data_contoh;
output :
ratarata_a = 3.6666666666667
tidak ada masalah dalam hal ini.
2. Kasus penjumlahan antar kolom (kolom_a + kolom_b)
SELECT id, ( data_a + data_b ) AS jumData FROM `data_contoh` GROUP BY id
Hasil :
ID 1 : 2+6 = 8
ID 2 : 4+NULL = NULL
ID 3 : 5+NULL = NULL
ID 4 : NULL+7 = NULL
nah, kelihatan juga kan ketika NULL dijumlahkan dengan berapapun nilainya akan “NULL”, demikian juga dengan pengurangan.
intinya null tidak hanya menegasikan dirinya sendiri, namun juga mempengaruhi perhitungan dengan menegasikan yang lainnya.
Sebagai solusi agar nilai NULL kita negasikan, yang berarti bukan NULL yang menegasikan kita.
ganti query sql berikut ini:
SELECT id, ( IFNULL(data_a,0) + IFNULL(data_b,0) ) AS jumData FROM `data_contoh` GROUP BY id
Hasil:
ID 1 : 2+6 = 8
ID 2 : 4+NULL = 4
ID 3 : 5+NULL = 5
ID 4 : NULL+7 = 7
Penjelasannya ada disini, Dalam mysql ada istilah Control Flow Functions, yang terdiri dari:
- CASE WHEN THEN
kondisi = operasi yang menghasilkan nilai tertentu, misal 1+1
SELECT CASE kondisi WHEN 1 THEN ’satu’ WHEN 2 THEN ‘dua’ ELSE ‘banyak’ END;
hasil : dua
- IF
kondisi = operasi yang menghasilkan nilai true/false, misal 10>11
SELECT IF(kondisi,’oke’,'not oke’);
hasil : not oke
- IFNULL
kondisi = jika kondisi berupa NULL maka ambil angka 9, jika kondisi tidak NULL ambil nilai kondisi, misal 12/0
SELECT IFNULL(kondisi,9);
hasil : 0
- NULLIF
kondisi = jika kondisi1=kondisi2 hasil=NULL, jika tidak hasil=kondisi1. misal kondisi1=5-2, kondisi2=6-3
SELECT NULLIF(kondisi1,kondisi2);
hasil : NULL
Cukup mudah bukan, yang penting jika ketemu NULL, maka pakai control flow sebagai solusinya.
Fuihhh ngantukk….
Desember 28th, 2009 at 19:54
Malam pak. Aku udah coba dan berhasil. Tapi waktu aku mencari rata-rata data_a + data_b dengan AVG koq gak bisa ya.
Aku beri perintah begini:
SELECT id, AVG( IFNULL(data_a,0) + IFNULL(data_b,0) ) AS jumData FROM `data_contoh` GROUP BY id
Hasilnya:
ID 1 : 8 seharusnya 4
ID 2 : 4 seharusnya 2
Gimana caranya biar hasilnya benar. Thx.
Desember 28th, 2009 at 19:59
Klo aku pakai:
SELECT id, IFNULL(data_a,0) + IFNULL(data_b,0))/2 AS jumData FROM `data_contoh` GROUP BY id
Hasilnya benar tetapi aku ingin pake AVG aja kayak AVERAGE di Excel karena lebih fleksibel. Thx pak.
Mei 3rd, 2010 at 14:16
mksih atas tutorialnya aku dah coba….di atas itu
kalo tuk menghilangkan nol 4 digit di belakang angka gimana yah
contoh nya kayak gini nih :
misalnya 1.0000 nah yang mau di hilangkan angka nol nya menjadi angka 1
mksih sebelumnya…….