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?

Sebenarnya masih banyak variasi perintah query di MySQL yang menggunakan fungsi GROUP BY dan HAVING, termasuk fungsi GROUP_CONCAT() yang pernah saya bahas di web ini. Silahkan Anda berkreasi berdasarkan contoh-contoh di atas. Semoga tutorial ini bermanfaat untuk kita semua. Amin

Berbagi itu indah...Share on Facebook0Share on Google+3Tweet about this on TwitterShare on LinkedIn0Pin on Pinterest0Digg this

54 comments

  1. Sekedar tambahan CMIIW,
    WHERE hanya bisa digunakan apabila key filter adalah field yang ada di table, bukan alias atau fungsi seperti contoh diatas.

    HAVING digunakan apabila key filter adalah alias.

    WHERE mem-filter dari sisi isi didalam table, sedangkan HAVING mem-filter dari sisi hasil query.

    SELECT a,b,c FROM tablex WHERE c = 1;
    SELECT a,b,c AS nama_alias FROM tablex HAVING nama_alias = 1;

    sekali lagi, CMIIW.

    1. Terima kasih atas tambahannya ya Lee, memang betul jika HAVING di MySQL dapat juga digunakan sebagai pengganti WHERE dengan field berupa alias. Namun sepertinya itu tidak berlaku di Oracle, HAVING harus digunakan bersamaan dengan fungsi agregat.

      cmiiw

  2. Wih trima kasih banget informasinya, fungsi HAVING ini mulai hari akan menjadi library di memory sy yang takkan pernah dilupakan (Soalnya saya baru tau :D).

    Sementara sebelumnya, sy kalo mau filter nilai hasil yag di simpan di alias, suka di temporarykan dulu di tabel sementara lalu di filter lagi.

    TERNYATA, hanya fungsi HAVING sebagai pengganti, SATU kata tapi mujarab…

    TQ….

  3. Mas misal untuk teknik average pada javascript+php gimana rumusnya? Misal pada nilai rata ujian=ujian tulis+ujian praktek/2.
    90+80=85 yang benar.
    jika 90+(-)=90 atau
    (-)+90=90
    gimana y mas…. ket.(-)kosong krn tidak ada ujian praktek.. tolong mas. plis…solusinya

    1. ya tinggal di-seleksi aja kondisinya, jika ada yang kosong maka faktor pembaginya juga dikurangin. misal: dari 10 nilai ada 2 yang kosong maka seluruh nilai ditotal lalu dibagi 8 (bukan dibagi 10)

  4. sore pak, saya mau minta tolong. jika ingin menjumlahkan nilai dari yang paling MAX ke MIN dari beberapa record. bagaimana pak dengan mysql?
    makasih sebelumnya…

  5. Permisi pak, numpang tanya
    apakah penggunaan UNION lebih cepat dari pada OR,
    klo ia mungkin bisa dikasih contohnya beserta artikelnya.

    Terima kasih

  6. mas ada yang punya tutorial n sourcode buat laporan penjualan seperti bentuk tabel dibawah pada php..?? Terima Kasih…
    ==================================================
    | No. Transaksi | Kode Barang | Nama Barang | Harga|Qty|Total |
    =================================================
    | Tr.001 | B001 | TV | 2000 |1 | ….. |
    | | B002 | Kulkas | 3000 |1 | ….. |
    | | B003 | Laptop | 4000 |1 | ….. |
    —————————————————————————————–

    1. membuat laporan tinggal masalah query-nya saja, kalo sudah menguasai query untuk menampilkan data tersebut saya kira ga ada masalah. selanjutnya tinggal laporannya mau berbentuk apa? tampilan, pdf, atau excel.

  7. mas ada yang punya tutorial n sourcode buat laporan penjualan seperti bentuk tabel dibawah pada php..?? Terima Kasih…
    —————————————————————————————–
    | No. Transaksi | Kode Barang | Nama Barang | Harga|Qty|Total |
    —————————————————————————————–
    | | B001 | TV | 2000 |1 | ….. |
    | Tr.001 |B002 | Kulkas | 3000 | 2 | ….. |
    | |B003 Laptop | 4000 }1 | ….. |
    |—————————————————————————————–—————|

  8. kalau yg mau ditampilkan misalkan cuman 3 matakuliah ja, misal algoritma, kalkulus dan pemrograman web gmana caranya??

  9. Kereeen tutorialnya,,langsung saya masukin di bookmark hehe..
    Mas Achmad klo mo buat peringkat berdasarkan nilai maximumnya gimana mas??
    thanks

  10. Makasih Mas dengan bantuan langkah-langkah mencari sebuah rata-rata dalam mysqlnya. Tapi mas mau nanya juga ni, gimana ya mas mencari nilai rata-rata satu buah matkul, jika dalam sebuah field ada nama matkul yang sama tapi dalam nilai berbeda.
    ex :
    +—————-+————-+
    | nama_mk | nilai_angka |
    +—————-+————-+
    | Pengenalan Web | 78 |
    | Pengenalan Web | 75 |
    | Pengenalan Web | 77 |
    | Pengenalan Web | 69 |
    | Pengenalan Web | 70 |
    | Pengenalan Web | 59 |
    | Pengenalan Web | 58 |
    | Pengenalan Web | 54 |
    | Pengenalan Web | 90 |
    | Pengenalan Web | 87 |
    | Basis Data 2 | 77 |
    | Basis Data 2 | 72 |
    | Basis Data 2 | 81 |
    | Basis Data 2 | 71 |
    | Basis Data 2 | 59 |
    | Basis Data 2 | 61 |
    | Basis Data 2 | 56 |
    | Basis Data 2 | 54 |
    | Basis Data 2 | 87 |
    | Basis Data 2 | 88 |
    +—————-+————-+
    20 rows in set (0.00 sec)

  11. Wah, templatenya bagusan jadi resep lama-lama disini. oiya pak. bisa sharing tentang optimasi database, teori normal form dan tips apa saja yang akan membuat excecute query jadi lebih cepat.! 😀

  12. luar biasa masnya ini, btw saya mau nanya, bagaimana saya menimpan proses data dgn kasus misal invoice, td di jelaskan nilai mahasiswa dgn menggunakan sum,
    lalu bila saya ingin menerapkan nilai sum untuk seluruh transaksi saya. misal sy pny contoh kasus tabel:
    inv(nama, tgl,total_inv)
    detail_inv(id,product,qty,price)
    kemudian sy ingin menyimpan fungsi SUM dari seluruh total Price dr tabel detail_inv ke dalam tabel inv dikolom total_inv bagaimana script bayangannya? mohon penjelasan, terimakasih

  13. Gan, sya lagi buat fungsi pnghitungan data pada tabel dengan filter ‘NAMA CS’ dan TGL..
    Tapi fungsi TGLnya belum jalan.. Kyk gini kodenya >>

    $where = ”;
    if (isset($_GET[‘tgl_awal’]) && $_GET[‘tgl_akhir’]){
    $tgl_awal=$_GET[‘tgl_awal’];
    $tgl_akhir=$_GET[‘tgl_akhir’];
    $where .= “where date_format(datetime,’%Y-%m-%d’) between ‘$tgl_awal’ and ‘$tgl_akhir’ “;}

    $query = “SELECT source, COUNT(source)
    FROM inquiry WHERE agent=’fendy chang’ GROUP BY source”.$where;
    $result = mysql_query($query) or die(mysql_error());

    Errornya seperti ini >> 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 ‘date_format(datetime,’%Y-%m-%d’) between ‘2015-09-01’ and ‘2015-09-08” at line 2

    Kira2 kalau bgitu salah dimana ya ?

  14. Gan bagaimana menampilkan data berdasarkan bulan. Saya punya tabel angsuran di dalam angsuran ada kd anggota dan tanggal angsur. Bagaimana cara menampilkan angsuran anggota tersebut berdasarkan bulan yang anggota angsur.

  15. gan ane mau nanya, kalo misalkan mau ngegabungin dua record berbentuk varchar gimana yaah??
    contoh : “afifal ” ” rivaldi” nah kalo ane klik proses maka akan muncul “afifal rivaldi” itu harus pake rumus kaya gimana yaah?? ane pake “=” malah nilainya true.
    mohon bantuannya gan

  16. selamat siang,
    mohon bantuan rekan2, saya punya tabel yang hasil dari mesin absen

    tabel mesin absen
    | nama | tanggal | jam
    | doni |2016-10-06 | 08:00:00
    | doni |2016-10-06 | 08:05:00
    | doni |2016-10-06 | 17:00:05
    | doni |2016-10-06 | 19:00:05

    saya ingin tanya bagaimana menampilkan data dan di simpan dalam tabel
    tabel absen
    | nama | tanggal | masuk | keluar
    | doni |2016-10-06 | 08:00:00 | 19:00:05

    terima kasih
    sukses buat rekan2 semua

  17. maaf mas kalau cara mencari nilai max bagaiamana ya
    no kata1 kata2 kata3
    1 46 50 20
    2 43 100 10
    nah jadi pada no 1 maxnya 50
    dan pada no 2 maxnya 100 gtu mas jadi pernomor gtu mas??
    mohon bantuannya

  18. Maaf mas mau nanya, kalok mau nampilin salah satu record yang duplikasi gi mana?
    +————+—————–+———-+——-+———————+
    | nim | kode_matakuliah | semester | grade | tanggal_terbuat |
    +————+—————–+———-+——-+———————+
    | 1210520126 | TSKK340127 | 4 | A | 2016-07-18 20:38:27 |
    | 1210520126 | TSKK340128 | 4 | C+ | 2016-07-18 20:38:27 |
    | 1210520126 | TSKB140229 | 4 | D | 2016-07-18 20:38:27 |
    | 1210520126 | TSKB240230 | 4 | C+ | 2016-07-18 20:38:27 |
    | 1210520126 | TSKB340232 | 4 | C+ | 2016-07-18 20:38:27 |
    | 1210520126 | TSKB440331 | 4 | A | 2016-07-18 20:38:27 |
    | 1210520126 | TSKB140434 | 4 | B | 2016-07-18 20:38:27 |
    | 1210520126 | TSKB240535 | 4 | A | 2016-07-18 20:38:27 |
    | 1210520126 | TSKB140536 | 4 | A | 2016-07-18 20:38:27 |
    | 1210520126 | TSKK360154 | 4 | A | 2016-07-18 20:38:28 |
    | 1210520126 | TSKB140229 | 4 | B+ | 2016-07-18 20:41:49 |
    +————+—————–+———-+——-+———————+
    pada tampilan di atas terdapat dua kode matakuliah TSKB140229, nah saya ingin mngambil kode_matakuliah yg terbaru yaitu kode_matakuliah yg gradenya B+ agar hasilnya seperti ini :
    +————+—————–+———-+——-+———————+
    | nim | kode_matakuliah | semester | grade | tanggal_terbuat |
    +————+—————–+———-+——-+———————+
    | 1210520126 | TSKK340127 | 4 | A | 2016-07-18 20:38:27 |
    | 1210520126 | TSKK340128 | 4 | C+ | 2016-07-18 20:38:27 |
    | 1210520126 | TSKB240230 | 4 | C+ | 2016-07-18 20:38:27 |
    | 1210520126 | TSKB340232 | 4 | C+ | 2016-07-18 20:38:27 |
    | 1210520126 | TSKB440331 | 4 | A | 2016-07-18 20:38:27 |
    | 1210520126 | TSKB140434 | 4 | B | 2016-07-18 20:38:27 |
    | 1210520126 | TSKB240535 | 4 | A | 2016-07-18 20:38:27 |
    | 1210520126 | TSKB140536 | 4 | A | 2016-07-18 20:38:27 |
    | 1210520126 | TSKK360154 | 4 | A | 2016-07-18 20:38:28 |
    | 1210520126 | TSKB140229 | 4 | B+ | 2016-07-18 20:41:49 |
    +————+—————–+———-+——-+———————+
    mohon Bantuannya mass.

  19. pak saya mau nanyak,saya mau buat syntac count: select count (nim) as jumlahnim from mahasiswa nah tapi pas di run yang keluarnya function name parsing and resolution.
    mohon pencerahannya pak

  20. Menampilkan semua barang harga terakhir transaksi pelanggan x, dari tabel transaksi, barang, pelanggan select max(t.tgl), s.nama, t.harga from trx t left join ….. group by s.idstk hasilnya g sesuai, ada cara yg bener ga om?

  21. Mas SOlihin,
    sya sering baca artikelnya.
    Sya mau tanya nih mhn pencerahannya.
    sya ada tabel dibawah ini.
    ——————
    Tabel :
    ID |tanggal |reg_qty |rep_qty
    37 |2016/11/30 21:11 |2323 |1221
    30 |2016/12/01 0:00 |6554 |45454
    2 |2016/12/02 22:09 |15518 |400
    3 |2016/12/04 20:17 |1176 |600
    38 |2016/12/04 22:00 |1222 |342
    39 |2016/12/05 5:29 |456 |5677
    4 |2016/12/05 17:00 |10834 |50
    ————————
    Dalam tabel tsb, datanya ribuan.
    sya ingin mengambil nilai max/ min. dari bulan xx tahun xx, nilai max/min dari (regqty) dan tanggal berapa. terus nilai dari regqty tsb disum berdasarkan tanggal dalam bulan.
    Jadi saya ambil nilai max dari 30 hari.

    Mhon perintah querynya mas.
    Terimakasih, smg bermanfaat.
    Aang

Leave a Reply

Your email address will not be published. Required fields are marked *