GROUP BY dan HAVING di SQL: Contoh Soal dan Error yang Sering Ada

Perbedaan WHERE dan HAVING di SQL ada pada waktunya: WHERE menyaring baris sebelum dikelompokkan, sedangkan HAVING menyaring kelompok setelah GROUP BY selesai menghitung agregatnya. Karena itu fungsi seperti AVG() atau COUNT() hanya boleh muncul di HAVING, tidak di WHERE. Artikel ini memakai satu tabel nilai kecil untuk semua contoh, lengkap dengan hasil query-nya, supaya kamu bisa mencocokkan sendiri.

#Tabel contoh yang dipakai di semua soal

Tabel nilai berisi 10 baris. Satu mahasiswa bisa muncul dua kali karena mengambil dua mata kuliah, Basis Data (BD) dan Algoritma dan Struktur Data (ASD). Dua baris sengaja punya skor kosong (NULL), misalnya karena nilainya belum diinput dosen.

Kalau mau mencoba sendiri, buat tabelnya di MySQL, PostgreSQL, atau SQLite, lalu jalankan query di bawah satu per satu.

#Contoh soal GROUP BY: COUNT, AVG, SUM per kelas dan mata kuliah

Soal: tampilkan jumlah baris, jumlah skor yang terisi, rata-rata, total, skor terendah, dan skor tertinggi untuk setiap kombinasi kelas dan mata kuliah.

SELECT kelas, kode_mk,
       COUNT(*)    AS jumlah_baris,
       COUNT(skor) AS skor_terisi,
       AVG(skor)   AS rata,
       SUM(skor)   AS total,
       MIN(skor)   AS terendah,
       MAX(skor)   AS tertinggi
FROM nilai
GROUP BY kelas, kode_mk
ORDER BY kelas, kode_mk;

Hasilnya empat baris, satu per kombinasi:

Lihat baris A, BD. Ada tiga mahasiswa (Ayu, Bima, Citra), tapi rata-ratanya 72.5, bukan 48.3. Fungsi agregat seperti AVG, SUM, MIN, dan MAX melewati NULL, jadi yang dihitung hanya (80 + 65) / 2 = 72.5. Nilai Citra tidak dianggap nol, melainkan tidak ada.

Di PostgreSQL, AVG pada kolom integer mengembalikan angka dengan banyak desimal (misalnya 72.5000000000000000). Bungkus dengan ROUND(AVG(skor), 2) kalau ingin tampilannya rapi.

#Perbedaan WHERE dan HAVING dengan dua query yang mirip

Dua query berikut terlihat hampir sama, tapi menjawab pertanyaan yang berbeda.

Query pertama memakai HAVING:

SELECT kelas, kode_mk, AVG(skor) AS rata
FROM nilai
GROUP BY kelas, kode_mk
HAVING AVG(skor) > 75;

Pertanyaannya: kelompok mana yang rata-ratanya di atas 75? Hasilnya tiga baris: A ASD (80.0), B ASD (77.5), dan B BD (80.0). Kelompok A BD dengan rata-rata 72.5 dibuang setelah dihitung.

Query kedua memakai WHERE:

SELECT kelas, kode_mk, AVG(skor) AS rata
FROM nilai
WHERE skor >= 70
GROUP BY kelas, kode_mk;

Pertanyaannya: kalau hanya skor 70 ke atas yang dihitung, berapa rata-rata tiap kelompok? Sekarang Bima (65) dan Dodi di ASD (60) dibuang sebelum pengelompokan, begitu juga dua baris NULL karena NULL >= 70 tidak bernilai benar. Hasilnya empat baris: A ASD 80.0, A BD 80.0 (hanya Ayu), B ASD 95.0 (hanya Fajar), dan B BD 80.0.

Keduanya bisa digabung. Tambahkan HAVING COUNT(*) >= 2 pada query kedua, maka A BD dan B ASD ikut terbuang karena setelah disaring masing-masing hanya punya satu baris. Sisanya A ASD dan B BD, masing-masing dengan rata-rata 80.0.

Aturan praktisnya: kalau syaratnya bisa diperiksa pada satu baris (skor, kelas, tanggal), taruh di WHERE. Kalau syaratnya baru bisa diperiksa setelah beberapa baris dijumlah atau dirata-rata, taruh di HAVING. Menaruh syarat baris di WHERE juga lebih hemat, karena database tidak perlu mengelompokkan baris yang toh akan dibuang.

#Kenapa alias di SELECT tidak bisa dipakai di WHERE?

Jawabannya ada di urutan eksekusi logis. Kamu menulis query mulai dari SELECT, tapi database memprosesnya dengan urutan lain:

1. FROM: ambil tabel nilai.
2. WHERE: buang baris yang tidak memenuhi syarat.
3. GROUP BY: kumpulkan baris tersisa ke dalam kelompok.
4. HAVING: buang kelompok yang tidak memenuhi syarat.
5. SELECT: hitung kolom yang ditampilkan dan beri alias.
6. ORDER BY: urutkan hasil.
7. LIMIT: potong jumlah baris.

Alias rata baru lahir di langkah 5. Saat WHERE berjalan di langkah 2, nama itu belum ada. Itulah sebabnya MySQL menolak WHERE rata > 70 dengan pesan Unknown column 'rata' in 'where clause', sementara ORDER BY rata berjalan lancar karena ORDER BY datang sesudah SELECT.

HAVING adalah wilayah abu-abu. Secara urutan logis ia datang sebelum SELECT, sehingga PostgreSQL menolak HAVING rata > 75 dan meminta kamu menulis ulang HAVING AVG(skor) > 75. MySQL justru mengizinkan alias di HAVING sebagai fitur tambahan. Kalau tugasmu dinilai dengan DBMS tertentu, ikuti DBMS itu. Kalau tidak jelas, tulis ulang ekspresi agregatnya di HAVING karena bentuk itu diterima di mana-mana.

#COUNT(*) vs COUNT(kolom) saat ada NULL

COUNT(*) menghitung baris. COUNT(skor) menghitung baris yang skornya tidak NULL. Bedanya terlihat jelas di tabel contoh:

SELECT kode_mk, COUNT(*) AS n, COUNT(skor) AS n_skor, AVG(skor) AS rata
FROM nilai
GROUP BY kode_mk;

Rata-rata BD adalah 305 / 4 = 76.25, bukan 305 / 5. Kalau dosen menetapkan bahwa nilai kosong dihitung nol, kamu harus menyatakannya secara eksplisit dengan COALESCE:

SELECT kode_mk, AVG(COALESCE(skor, 0)) AS rata_isi_nol
FROM nilai
GROUP BY kode_mk;

Hasilnya ASD 63.0 (315 / 5) dan BD 61.0 (305 / 5). Selisih belasan poin itu cukup untuk mengubah kesimpulan laporan, jadi tentukan dulu arti NULL di soalmu.

Satu variasi lagi yang sering keluar di ujian adalah COUNT(DISTINCT nim). Query SELECT kelas, COUNT(DISTINCT nim) FROM nilai GROUP BY kelas menghasilkan 3 untuk kelas A dan 3 untuk kelas B. Kelas A punya 5 baris, tapi Ayu dan Bima muncul dua kali, jadi mahasiswanya hanya tiga.

#Error GROUP BY yang paling sering muncul dan cara memperbaikinya

#1. Kolom non-agregat tidak ada di GROUP BY

SELECT nim, nama, AVG(skor)
FROM nilai
GROUP BY nim;

PostgreSQL menolak dengan pesan column "nilai.nama" must appear in the GROUP BY clause or be used in an aggregate function. MySQL versi baru dengan mode ONLY_FULL_GROUP_BY (aktif secara bawaan) memberi error 1055 dengan isi yang mirip. Logikanya: satu kelompok nim bisa berisi beberapa baris, dan database tidak tahu nama mana yang harus ditampilkan. Perbaikannya, tambahkan nama ke GROUP BY. Pengecualian ada kalau kolom yang dikelompokkan adalah primary key tabel itu, karena PostgreSQL dan MySQL modern tahu bahwa kolom lain pasti bergantung padanya.

#2. Fungsi agregat di WHERE

SELECT kelas, AVG(skor) FROM nilai
WHERE AVG(skor) > 75
GROUP BY kelas;

PostgreSQL membalas aggregate functions are not allowed in WHERE, MySQL membalas Invalid use of group function. Pindahkan syaratnya ke HAVING.

#3. Memakai alias di WHERE

Sudah dibahas di atas. Tulis ulang ekspresinya, atau bungkus query dalam subquery lalu saring di luar.

#4. Mengira COUNT(kolom) sama dengan COUNT(*)

Tidak ada error yang muncul, dan justru itu bahayanya. Angkanya keliru diam-diam setiap kali ada NULL.

#5. Lupa ORDER BY

GROUP BY tidak menjamin urutan hasil. Kalau hasilnya kebetulan urut di laptopmu, itu bukan jaminan di server asisten dosen.

#Soal latihan gabungan dan jawabannya

Soal: tampilkan nim, nama, jumlah skor yang terisi, dan rata-rata skor untuk mahasiswa yang sudah punya nilai di dua mata kuliah, urutkan dari rata-rata tertinggi.

SELECT nim, nama, COUNT(skor) AS mk_dinilai, AVG(skor) AS rata
FROM nilai
GROUP BY nim, nama
HAVING COUNT(skor) = 2
ORDER BY rata DESC;

Citra dan Fajar tidak muncul karena masing-masing hanya punya satu baris. Eka punya dua baris, tetapi skor ASD-nya NULL, jadi COUNT(skor) untuk Eka bernilai 1. Kalau kamu menulis COUNT(*) = 2, Eka ikut muncul dengan rata-rata 70.0 dari satu nilai saja. Perhatikan juga Bima dan Dodi yang sama-sama 75.0. Urutan keduanya tidak dijamin, jadi tambahkan , nim di ORDER BY kalau hasilnya harus selalu sama.

GROUP BY hampir selalu datang bersama JOIN di soal yang lebih panjang, misalnya menghitung jumlah mata kuliah per dosen. Pola JOIN-nya dibahas di contoh soal SQL JOIN. Kalau tabel nilaimu sendiri masih berantakan, misalnya nama mahasiswa tersimpan berulang di tiap baris seperti contoh di atas, baca normalisasi 1NF sampai 3NF dan cara memecah relasi many-to-many.

#FAQ

#Apakah HAVING bisa dipakai tanpa GROUP BY?

Bisa. Seluruh tabel lalu dianggap satu kelompok. SELECT COUNT(*) FROM nilai HAVING COUNT(*) > 5 mengembalikan satu baris berisi 10, sedangkan kalau syaratnya > 20, hasilnya kosong. Bentuk ini jarang dipakai, tapi kadang muncul sebagai soal jebakan.

#Apakah urutan kolom di GROUP BY memengaruhi hasil?

Tidak memengaruhi isi kelompok. GROUP BY kelas, kode_mk dan GROUP BY kode_mk, kelas menghasilkan empat kelompok yang sama. Urutan tampilannya yang bisa berbeda, dan itu diatur oleh ORDER BY.

#Mana yang lebih cepat, menyaring di WHERE atau di HAVING?

Untuk syarat yang berlaku per baris, WHERE hampir selalu lebih efisien karena baris dibuang sebelum dikelompokkan. Menaruh HAVING kelas = 'A' memang berjalan di banyak DBMS, tetapi database mengelompokkan dulu semua kelas lalu membuang sebagian.

#Bolehkah satu query memakai WHERE dan HAVING sekaligus?

Boleh, dan sering. Contohnya di atas: WHERE skor >= 70 membuang baris dulu, lalu HAVING COUNT(*) >= 2 membuang kelompok yang terlalu kecil.

#Kenapa SUM mengembalikan NULL, bukan 0?

Kalau semua baris dalam satu kelompok bernilai NULL, atau kelompoknya kosong, SUM mengembalikan NULL. Pakai COALESCE(SUM(skor), 0) kalau laporanmu harus menampilkan angka nol.