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

Comments
2 Comments

2 komentar:

Ngrayudan Bersatu mengatakan...

mampir juga gan
==============================
http://ngrayudan.blogspot.com
-download
-software
-pes 2012
-movie
-business (beenero.com)

Bpk radi mengatakan...

Buat saudara punya permasalahan ekonomi: hub aki santoro,karna saya sudah membuktikan bantuan aki santoro yang berminat,hub aki santoro di nomor 0823 1294 9955, atau KLIK DI SINI

Posting Komentar