2 Fungsi GROUP BY dan HAVING untuk Pengelompokkan Data di MySQL

Database MySQL merupakan salah perangkat lunak untuk penyimpanan data yang sangat populer untuk saat ini. Salah satu alasan banyak orang memilih MySQL adalah keunggulan dan kelengkapan fitur-fitur di dalamnya. Salah satu fitur dari MySQL adalah dukungan terhadap berbagai fungsi agregat GROUP BY dan HAVING untuk pengelompokkan data. Pada tutorial ini dijelaskan mengenai fitur GROUP BY dan HAVING tersebut, disertai contohnya.

Sebelum mencoba fungsi GROUP BY dan HAVING, perlu diketahui bahwa keduanya sangat berkaitan dengan fungsi agregat. Fungsi agregat merupakan kelompok fungsi di MySQL yang memungkinkan untuk memiliki parameter berupa kelompok data. Sebagai contoh dari fungsi agregat adalah fungsi SUM() yang akan menjumlahkan seluruh nilai yang menjadi parameternya. Contoh lain adalah fungsi MAX() yang akan menghasilkan nilai terbesar dari keseluruhan nilai yang menjadi parameternya.

Berikut ini beberapa fungsi agregat yang ada di MySQL:

Name Description
AVG() Return the average value of the argument
BIT_AND() Return bitwise and
BIT_OR() Return bitwise or
BIT_XOR() Return bitwise xor
COUNT(DISTINCT) Return the count of a number of different values
COUNT() Return a count of the number of rows returned
GROUP_CONCAT() Return a concatenated string
MAX() Return the maximum value
MIN() Return the minimum value
STD() Return the population standard deviation
STDDEV() Return the population standard deviation
SUM() Return the sum
VARIANCE() Return the population standard variance

Untuk memudahkan pemahaman bagaimana fungsi GROUP BY dan HAVING digunakan, kita akan menggunakan contoh data di MySQL. Buatlah tabel nilai yang menyimpan data nilai mahasiswa, strukturnya sebagai berikut:

Column Type Comment
nim varchar(10)  Nim mahasiswa
nama varchar(30)  Nama mahasiswa
matkul varchar(20)  Nama Matakuliah
nilai int(3)  Nilai mahasiswa

Selanjutnya isi tabel tersebut dengan beberapa data. Bagi yang ga mau repot bikin, ini sudah disiapkan perintah untuk bikin tabel dan mengisi datanya dalam bentuk file .sql. Silahkan download nilai.sql.

Berikut ini beberapa contoh persoalan yang dapat diselesaikan dengan fungsi GROUP BY dan HAVING.

Contoh Kasus #1. Tampilkan rata-rata nilai dari seluruh mahasiswa.

Untuk menyelesaikannya tidak perlu menggunakan GROUP BY karena yang diminta adalah seluruh mahasiswa. Untuk mendapatkan rata-rata nilai, kita dapat menggunakan fungsi AVG(). Berikut ini query untuk kasus tersebut.

SELECT AVG(nilai) as rata_rata FROM nilai;

Berikut ini hasil dari query di atas.

+-----------+
| rata_rata |
+-----------+
| 79.6800   |
+-----------+
Contoh Kasus #2. Tampilkan rata-rata nilai untuk setiap mahasiswa

Karena yang diminta adalah rata-rata untuk setiap mahasiswa, maka kita harus menggunakan GROUP BY untuk mengelompokkan rata-rata berdasarkan field tertentu. Query dan hasilnya kurang lebih sebagai berikut:

SELECT nim, nama, AVG(nilai) as rata_rata FROM nilai GROUP BY nim;
+------------+------+-----------+
| nim        | nama | rata_rata |
+------------+------+-----------+
| 0911500101 | ADI  | 82.0000   |
| 0911500102 | IDA  | 81.0000   |
| 0911500103 | EDI  | 80.6000   |
| 0911500104 | INA  | 70.4000   |
| 0911500105 | ANI  | 84.4000   |
+------------+------+-----------+
Contoh Kasus #3. Tampilkan nilai terbesar dan terkecil untuk setiap mahasiswa.

Query untuk menyelesaikan kasus ketiga di atas sebenarnya sama saja dengan yang sebelumnya. Perbedaannya hanya pada fungsi agregat yang digunakan untuk menampilkan nilai terbesar dan terkecil yaitu MAX() dan MIN(). Berikut ini query dan hasil query-nya.

SELECT nim, nama, MAX(nilai) as terbesar, MIN(nilai) as terkecil FROM nilai GROUP BY nim;
+------------+------+----------+----------+
| nim        | nama | terbesar | terkecil |
+------------+------+----------+----------+
| 0911500101 | ADI  | 90       | 65       |
| 0911500102 | IDA  | 90       | 70       |
| 0911500103 | EDI  | 88       | 60       |
| 0911500104 | INA  | 80       | 50       |
| 0911500105 | ANI  | 92       | 68       |
+------------+------+----------+----------+
Contoh Kasus #4. Tampilkan rata-rata nilai yang didapat mahasiswa untuk setiap matakuliah

Cukup jelas bahwa pada kasus ini, mirip dengan kasus kedua di atas, namun pengelompokkan data berdasarkan matakuliah, bukan berdasarkan mahasiswa. Querynya kurang lebih sebagai berikut:

SELECT matkul, AVG(nilai) as rata_rata FROM nilai GROUP BY matkul;
+-----------------+-----------+
| matkul          | rata_rata |
+-----------------+-----------+
| ALGORITMA       | 84.4000   |
| KALKULUS        | 62.6000   |
| PBO             | 82.0000   |
| PEMROGRAMAN WEB | 86.4000   |
| PTI             | 83.0000   |
+-----------------+-----------+
Contoh Kasus #5. Tampilkan rata-rata nilai untuk setiap mahasiswa, yang rata-rata nilai lebih besar dari 80

Perhatikan kembali kasus kedua di atas. Hasil query menunjukkan bahwa untuk setiap mahasiswa akan ditampilkan nilai rata-rata yang diperoleh. Nah pada kasus ke-5 ini yang ingin ditampilkan adalah hanya mahasiswa yang nilainya lebih dari 80. Artinya untuk mahasiswa atas nama “INA” yang mendapat nilai rata-rata 70.4 tidak boleh ditampilkan. Solusinya adalah dengan menambahkan kondisi pada query kasus kedua diatas. Nah, jika kondisi suatu query terkait dengan fungsi agregat, maka kita tidak bisa menggunakan kondisi WHERE. Penggunaan WHERE pada fungsi agregat akan menyebabkan error.

Cobalah query berikut ini.

SELECT nim, nama, AVG(nilai) as rata_rata FROM nilai GROUP BY nim WHERE AVG(nilai)>80;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE AVG(nilai)>80' at line 1

Lalu bagaimana solusinya jika tidak bisa menggunakan WHERE ? MySQL (dan juga database yang lainnya) memiliki struktur kondisi khusus terkait fungsi agregat yaitu HAVING. Jika query diatas diperbaiki dan menggunakan HAVING, maka hasilnya sebagai berikut:

SELECT nim, nama, AVG(nilai) as rata_rata FROM nilai GROUP BY nim HAVING AVG(nilai)>80;
+------------+------+-----------+
| nim        | nama | rata_rata |
+------------+------+-----------+
| 0911500101 | ADI  | 82.0000   |
| 0911500102 | IDA  | 81.0000   |
| 0911500103 | EDI  | 80.6000   |
| 0911500105 | ANI  | 84.4000   |
+------------+------+-----------+

Gampang kan?

Silahkan Anda berkreasi berdasarkan contoh-contoh di atas. Semoga tutorial ini bermanfaat untuk kita semua. Amin

 

Sumber

1 7 Variasi Perintah INSERT di MySQL yang Sering Terlupakan

Data Manipulation Language (DML) merupakan bagian dari perintah SQL (Structured Query Language) yang terdiri dari berbagai perintah untuk memanipulasi data di dalam suatu database. Empat perintah utama yang termasuk dalam perintah DML adalah perintah SELECT, INSERT, UPDATE dan DELETE. MySQL sebagai salah satu software database terkemuka tentunya mendukung keempat perintah DML tersebut dengan sangat baik. Di dalam tutorial kali ini, akan dibahas mengenai perintah INSERT dimana ternyata cukup banyak variasi perintah INSERT di MySQL yang sering terlupakan (terabaikan), namun sebenarnya sangat berguna.

Selain bentuk dasar perintah INSERT, setidaknya ada 7 (tujuh) variasi bentuk perintah INSERT yang  suatu saat akan berguna untuk Anda. Berikut ini ketujuh perintah INSERT tersebut yang selanjutnya akan dijelaskan satu per satu.

  1. INSERT INTO (field1, field2, …) VALUES (…)
  2. INSERT … SELECT …
  3. INSERT IGNORE …
  4. INSERT DELAYED …
  5. INSERT LOW PRIORITY | HIGH PRIORITY …
  6. INSERT … ON DUPLICATE KEY UPDATE
  7. REPLACE INTO …

Untuk mempermudah pemahaman terhadap tutorial ini, maka seluruh contoh yang disajikan akan mengacu pada struktur tabel sebagai berikut:

Nama tabel: tblmhs

mysql> desc tblmhs;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| nim      | varchar(10) | NO   | PRI | NULL    |       |
| nama     | varchar(40) | NO   |     | NULL    |       |
| alamat   | text        | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Di sini tidak dibahas mengenai bagaimana membuat struktur tabel di atas.

Bentuk Dasar Perintah INSERT

Sebelum membahas variasi dari perintah INSERT, alangkah baiknya kita mengingat kembali mengenai bagaimana bentuk dasar dari perintah INSERT tersebut. Perintah INSERT pada dasarnya merupakan perintah yang dapat digunakan untuk memasukkan data (record) ke sebuah tabel di database. Bentuk perintah ini cukup sederhana. Berikut ini contoh sederhana perintah INSERT untuk memasukkan data seorang mahasiswa dengan nim ‘1011500100‘, nama ‘ACHMAD SOLICHIN‘ dan alamat di JAKARTA SELATAN.

INSERT INTO tblmhs VALUES('1011500100', 'ACHMAD SOLICHIN', 'JAKARTA SELATAN');

Hal yang penting dari contoh perintah INSERT di atas adalah urutan dari nilai data yang akan ditambahkan harus sama dengan urutan field (kolom) di tabel. Urutan field di dalam tabel tblmhs adalah nim, nama lalu alamat, sehingga waktu melakukan INSERT urutan seharusnya juga nim, nama dan alamat. Jika urutan tidak sesuai, maka data yang dimasukkan bisa jadi menempati kolom yang salah juga

#1. INSERT INTO tabel (field1, field2) VALUES …

Pada bentuk dasar perintah INSERT di atas, kita harus menyebutkan nilai dari seluruh kolom. Jadi jumlah nilai yang akan ditambahkan harus sesuai dengan jumlah kolom dalam tabel. Namun ada kalanya kita hanya ingin menambahkan sebagian dari kolom tabel, jadi tidak semua kolom kita masukkan. Jika demikian, variasi perintah INSERT yang pertama ini tentu tepat untuk digunakan. Kita menyebutkan kolom-kolom yang akan di-insert-kan di belakang nama tabel. Urutannya juga tidak harus sesuai dengan urutan di dalam struktur tabel. Sebagai contoh, kita akan memasukkan data nim dan nama seorang mahasiswa, tanpa memasukkan alamatnya. Urutan fieldnya sengaja dibalik, nama terlebih dahulu baru nim. Berikut perintahnya:

INSERT INTO tblmhs (nama, nim) VALUES('CHOTIMATUL MUSYAROFAH', '1012501983');

Terlihat pada contoh di atas bahwa urutan yang harus disesuaikan adalah urutan field yang berada di belakang nama tabel ‘tblmhs’ dan urutan data (values) yang akan di-insert-kan.

#2. INSERT … SELECT …

Ada kalanya kita ingin memindahkan atau meng-copy sejumlah data yang berada pada suatu tabel ke tabel yang lainnya. Singkatnya copy data antar tabel. Untuk menyelesaikan permasalahan tersebut, tentunya sangat kurang efektif jika kita harus melakukan insert satu per satu. Untuk melakukan copy record antar tabel, akan lebih efektif jika kita menggunakan bentuk perintah “INSERT … SELECT …” yang menggabungkan proses insert (menambahkan data) dan select (mengambil data). Sebagai contoh, kita ingin memindahkan data nim dan nama mahasiswa yang berada di tabel tblpendaftaran ke tabel tblmhs, berikut ini perintahnya:

INSERT INTO tblmhs(nim, nama) SELECT nim, nama FROM tblpendaftaran;

#3. INSERT IGNORE

Penulis pernah membuat suatu absensi pada suatu perusahaan dimana data absensi diimport dari suatu file teks hasil keluaran dari mesin finger. Setelah diimport, data absensi karyawan akan diperiksa dan dilengkapi oleh staf HRD. Proses import dari teks file dapat terjadi berulang-ulang sehingga diperlukan mekanisme agar pada saat proses import data yang mungkin sudah diedit oleh staf HRD tidak tertimpa. Jadi intinya, saat proses import, harus diperiksa apakah data absensi yang akan dimasukkan ke tabel sudah ada atau belum. Jika sudah ada, maka tidak perlu dilakukan insert, namun jika tidak ada maka lakukan proses import. Awalnya terpikir untuk melakukan pengecekan data ke database terlebih dahulu sebelum insert, namun dari isi efektivitas tentu hal ini tidak baik, apalagi jika datanya cukup banyak. Beruntung kemudian ketemu bentuk perintah INSERT IGNORE … di MySQL. Dengan perintah ini, permasalahan tersebut dapat terselesaikan hanya dengan satu perintah.

Dalam contoh berikut ini, akan melakukan insert data ke tabel tblmhs hanya jika data nim belum ada di tabel. Pemeriksaan data didasarkan pada field yang menjadi primary key. Jadi jika terjadi duplikasi primary key maka data akan diabaikan (ignored).

INSERT IGNORE INTO tblmhs VALUES('1012501983','BUNGA', 'TANGERANG');

#4. INSERT DELAYED

Bentuk perintah INSERT DELAYED … merupakan bentuk perintah yang mungkin akan kita perlukan saat membuat suatu aplikasi transaksional dengan beberapa pengguna dalam satu waktu. Dengan tambahan perintah “DELAYED” maka proses menambahkan data akan ditangguhkan hingga tidak ada user yang mengakses tabel. Contoh berikut ini menggambarkan bagaimana perintah tersebut:

INSERT DELAYED INTO tblmhs (nim, nama) VALUES ('1011500121', 'LINTANG', 'TANGERANG');

Penjelasan lebih lanjut mengenai perintah ini dapat dilihat di manual MySQL.

#5. INSERT LOW PRIORITY | HIGH PRIORITY

Tambahan perintah LOW PRIORITY atau HIGH PRIORITY dibelakang perintah INSERT dapat digunakan untuk mengatur prioritas dari perintah INSERT yang akan dijalankan. Perintah dengan prioritas lebih tinggi tentu akan didahulukan untuk dijalankan. Sama seperti bentuk perintah INSERT DELAYED, perintah ini juga berguna saat kita “bermain” dengan proses transaksional. Berikut ini contoh sederhananya:

INSERT INTO tblmhs VALUES('1111500111', 'INDAH', 'JAKARTA');

#6. INSERT … ON DUPLICATE KEY UPDATE

Variasi perintah INSERT ini mungkin suatu saat akan kita perlukan. Misalnya pada kasus dimana pada proses insert sejumlah data, jika datanya sudah ada, dalam arti terjadi duplikasi primary key, maka lakukan perintah update untuk kolom tertentu. Sebagai contoh, kita akan melakukan penambahan data ke tblmhs, jika datanya sudah ada, maka update field alamat dengan alamat yang baru. Berikut ini perintahnya:

INSERT INTO tblmhs (nim, nama, alamat) VALUES('1111500111', 'WULAN', 'MALANG') ON DUPLICATE KEY UPDATE alamat='JAKARTA';

Untuk penjelasan lebih rinci terkait perintah ini dapat dilihat di dokumentasi MySQL (http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html).

#7. REPLACE INTO …

Penulis sangat senang dengan adanya bentuk perintah REPLACE di MySQL ini. Perintah ini sangatlah membantu. Contohnya saat penulis ingin melakukan penambahan data secara massal seperti pada kasus import absensi karyawan di atas. Jika pada proses import, kita menginginkan agar data selalu up to date, maka jika data sudah ada harus dilakukan proses update. Perintah REPLACE INTO berperan untuk melakukan dua tugas sekaligus, yaitu INSERT jika datanya belum ada dan UPDATE jika datanya sudah ada. Berikut ini contoh perintah ini:

REPLACE INTO tblmhs VALUES('1011500100', 'ACHMAD SOLICHIN', 'TANGERANG SELATAN');

Demikian ketujuh bentuk variasi perintah INSERT di dalam MySQL yang sering terlupakan namun seringkali kita butuhkan. Semoga postingan ini bermanfaat untuk kita semua. Mari terus semangat berbagi demi Indonesia!

 

Sumber

6 7 Rahasia CSS yang (mungkin) belum anda ketahui dan wajib diketahui

CSS pasti sudah menjadi hal yang wajib dipelajari jika anda ingin membuat website. Sekarang saya akan memberitahu rahasia CSS yang mungkin belum anda ketahui dan wajib diketahui, karena dapat memperkecil ukuran file CSS anda. Inilah dia rahasia CSS yang (mungkin) belum anda ketahui:

1. Memasang 2 class pada 1 tag HTML

Hei, apa anda tau bahwa kita bisa memasang 2 class untuk satu tag HTML? Jawabannya bisa.. Jika kita biasanya hanya membuat kode seperti ini:

<p class="content">

Maka jika kita ingin bahwa <p> mengikuti dua aturan class yang berbeda maka kita cukup mengubahnya menjadi:

<p class="content isi">

Jadi jika anda memiliki class content dan isi maka kedua aturan pada class tersebut akan dimasukkan kedalam <p>. Ingat hanya 2 tidak bisa lebih dan dipisahkan dengan spasi.

2. Hilangkan px untuk nilai 0

Oke, katakanlah anda memiliki kode CSS seperti ini:

.box {
border-top:10px;
border-left:0px;
padding:0px;
}

px disini berarti satuannya pixel dan kita harus menuliskannya, tetapi ternyata ada pengecualian untuk angka 0 (angka 0 saja) jadi kita bisa menuliskannya seperti ini:

.box {
border-top:10px;
border-left:0;
padding:0;
}

Ya, angka 0 tidak perlu menggunakan px, ini berarti kita memperkecil sedikit ukuran file CSS kita :)

3. Gunakanlah selalu shorthand

Apa sih shorthand? Shorthand merupakan aturan di CSS untuk menyingkat kode CSS anda. margin, dan padding merupakan contoh yang bisa kita singkat. Perhatikan contoh CSS berikut ini:

.box {
border-top:10px;
border-left:0;
border-right:20px;
border-bottom:15px;
padding-top:0;
padding-bottom:10px;
padding-left:12px;
padding-right:9px;
}

Dapat anda singkat menjadi:

.box {
border:10px 20px 15px 0;
padding:0 9px 10px 12px;
}

Lihat berapa banyak yang bisa kita hemat :) yang paling penting adalah urutannya dari kiri ke kanan dimulai dari posisi atas kemudian bergerak searah jarum jam.

4. Shorthand pada warna

Yap, warna pun bisa kita singkat, jika kita memiliki warna putih maka kita menggunakan #ffffff bisa disingkat menjadi #fff. Demikian juga jika kita memiliki warna #111111 menjadi #111. Tapi kita tidak bisa menyingkat warna #abcdef. Harus warna yang memiliki angka hexa yang sama.

5. Shorthand pada font

Siapa bilang font tidak bisa disingkat? Jika anda memiliki kode seperti ini:

.box {
font-style: italic;
font-weight: bold;
font-variant: small-caps;
font-size: 1em;
line-height: 1.5em;
font-family: verdana,sans-serif;
}

Dapat anda singkat menjadi 1 baris: font: bold italic small-caps 1em/1.5em verdana,sans-serif;
Perlu diingat bahwa untuk menyingkat font anda wajib mengatur font-size dan font-family yang diletakkan paling akhir. Untuk yang lainnya tidak wajib ada.

6. Menyingkat yang sudah disingkat

Kita bisa menyingkat yang sudah disingkat. Sudah singkat tapi masih bisa disingkat lagi? Nggak masuk akal.. Bisa. Perhatikan contoh berikut ini:

.box {
border-top:10px;
border-left:20px;
border-right:20px;
border-bottom:10px;
padding-top:0;
padding-bottom:0;
padding-left:15px;
padding-right:15px;
}

Oke, jika kode diatas disingkat maka hasilnya seperti ini:

.box {
border:10px 20px 10px 20px;
padding:0 15px 0 15px;
}

Dan karena nilai atas dan bawah serta kiri dan kanan nya sama. Maka bisa disingkat lagi menjadi:

.box {
border:10px 20px;
padding:0 15px;
}

Sip kan.. Ingat ini hanya berlaku jika nilai atas dan bawah serta kiri dan kanan sama.

7. Menengahkan objek

Anda ingin menengahkan suatu objek, misal images atau div. Gampang gunakan saja auto pada margin. Perhatikan contoh berikut ini:

#content{
width: 600px;
margin: 0 auto;
} 

auto disini berarti kita membiarkan CSS mendeteksi lebar kiri dan kanan secara otomatis berdasarkan lebar layar. Maka otomatis hasilnya akan ketengah. Tidak berlaku jika kita ingin menengahkan secara vertikal.

Bagaimana menarik bukan? itu baru sebagian kecil, dan masih banyak lagi rahasia dan trik CSS yang perlu diketahui.

Sumber

11 Ajax Sederhana Menggunakan JQuery

AJAX adalah sebuah teknik pemrograman yang memungkinkan kita melakukan pertukaran data dengan server di belakang layar, sehingga halaman web tidak harus dipanggil ulang hanya untuk mengganti sebagian kecil dari isi halaman. Dengan framework jQuery, proses development ajax bisa menjadi lebih mudah. Salah satu fungsi di jQuery untuk mempermudah penggunaan ajax adalah fungsi load(), yang memiliki 3 buah parameter sbb:

load(url, params, callback)

    keterangan:
  • url (String): URL dari file html yang akan di load
  • params (Object): Data yang akan dikirimkan ke server (optional)
  • callback (Function): Fungsi yang akan dijalankan ketika data berhasil di load (optional)
Contoh Penggunaan

$(function(){
     $("#menu a").click(function() {
          url = $(this).attr("href");
          $("#container").load(url);
          return false;
     });
     $(document).ajaxStart(function(){
          $("#loading").fadeIn();
          $("#container").fadeIn(function(){
               $(this).css({"opacity":"0.1"});
          });
     });
     $(document).ajaxComplete(function(){
          $("#loading").hide();
          $("#container").css({"opacity":"1"});
     });
});

    keterangan:
  • #menu a, adalah link berupa elemen a didalam id menu yang akan di klik
  • url = $(this).attr('href'); adalah isi variabel url yang merupakan isi dari atribut link berupa href
  • $('#container').load(url); adalah funsi untuk menampilkan content dari url yang di klik ke elemen ber-id container
Untuk mempercantik tampilan loading sebelum data ajax terload semua, saya menambahkan fungsi tertentu ketika ajax start dan ajax complete.

Untuk lebih jelasnya silakan lihat demo Demo Atau Anda bisa mendownload source code nya di sini: Download referensi: gawibowo