Fungsi agregat dari pelajaran sebelumnya meringkas seluruh tabel jadi satu angka. Di tabel karyawan, hasilnya 8 orang dengan rata-rata gaji 7,3 juta. Tapi pertanyaan HRD biasanya lain: tiap departemen ada berapa orang, dan rata-rata gajinya berapa?
Buat itu ada GROUP BY. Dia mengumpulkan baris yang nilainya sama di satu kolom jadi satu kelompok, lalu fungsi agregat (COUNT, AVG, SUM, ...) menghitung per kelompok, bukan untuk seluruh tabel. Kayak tumpukan slip gaji di meja HRD: slipnya dipilah per departemen jadi tiga tumpukan, lalu tiap tumpukan dihitung, berapa lembar dan berapa rata-rata gajinya. HAVING itu langkah sesudahnya: singkirkan tumpukan yang isinya kurang dari tiga.
Tanpa GROUP BY: satu kelompok besar
Tabel karyawan di Lab dan latihan berisi 8 orang:
| id | nama | departemen | gaji |
|---|---|---|---|
| 1 | Budi | IT | 8000000 |
| 2 | Siti | IT | 9000000 |
| 3 | Andi | IT | 8500000 |
| 4 | Dewi | HR | 6000000 |
| 5 | Rizky | HR | 6500000 |
| 6 | Maya | HR | 6200000 |
| 7 | Dimas | Marketing | 7000000 |
| 8 | Citra | Marketing | 7200000 |
SELECT departemen, COUNT(*) AS jumlah, AVG(gaji) AS rata_gaji
FROM karyawan;
| departemen | jumlah | rata_gaji |
|---|---|---|
| IT | 8 | 7300000 |
Sama kayak di pelajaran Fungsi Agregat, semua 8 baris jadi satu kelompok, jadi hasilnya 1 baris. IT di kolom departemen cuma nilai yang diambil SQLite dari salah satu baris, bukan ringkasan. MySQL dan PostgreSQL bahkan menolak query ini.
GROUP BY: satu baris per kelompok
SELECT departemen, COUNT(*) AS jumlah, AVG(gaji) AS rata_gaji
FROM karyawan
GROUP BY departemen;
| departemen | jumlah | rata_gaji |
|---|---|---|
| HR | 3 | 6233333.333333333 |
| IT | 3 | 8500000 |
| Marketing | 2 | 7100000 |
GROUP BY departemenbikin satu kelompok untuk tiap nilaidepartemenyang berbeda: IT, HR, Marketing.COUNT(*)danAVG(gaji)dihitung di dalam tiap kelompok. IT: (8.000.000 + 9.000.000 + 8.500.000) / 3 = 8.500.000.- Hasilnya satu baris per kelompok: 3 baris.
- Kolom biasa di
SELECT(di sinidepartemen) harus kolom yang ada diGROUP BY. Kolom lain harus dibungkus fungsi agregat.
HAVING: saring kelompok
SELECT departemen, COUNT(*) AS jumlah, AVG(gaji) AS rata_gaji
FROM karyawan
GROUP BY departemen
HAVING COUNT(*) > 2;
| departemen | jumlah | rata_gaji |
|---|---|---|
| HR | 3 | 6233333.333333333 |
| IT | 3 | 8500000 |
HAVING ngecek tiap kelompok setelah dihitung. Marketing cuma punya 2 orang, jadi dibuang. Tinggal IT dan HR.
WHERE atau HAVING
WHEREmenyaring baris sebelum dikelompokkan.HAVINGmenyaring kelompok sesudah dihitung.
Coba tebak: SELECT departemen, COUNT(*) FROM karyawan WHERE COUNT(*) > 2 GROUP BY departemen; jalan gak?
SQLite mengerjakan query dengan urutan ini: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. Saat WHERE jalan, kelompoknya belum ada, jadi COUNT(*) belum bisa dihitung. Makanya SQLite menolak dengan misuse of aggregate: COUNT(). Syarat yang memakai fungsi agregat ditulis di HAVING.
Keduanya boleh dipakai bersama:
SELECT departemen, COUNT(*) AS jumlah
FROM karyawan
WHERE gaji > 6300000
GROUP BY departemen
HAVING COUNT(*) > 2;
WHERE membuang Dewi (6.000.000) dan Maya (6.200.000) dulu, jadi HR tinggal 1 orang. Setelah itu HAVING cuma meloloskan IT.
💡 Beda SQLite, MySQL, dan PostgreSQL
- Kolom yang gak dikelompokkan.
SELECT departemen, nama, COUNT(*) FROM karyawan GROUP BY departemen;jalan di SQLite:namadiisi satu nama dari tiap kelompok (Dewi, Budi, Dimas). MySQL (5.7 ke atas, pengaturan bawaan) dan PostgreSQL menolak. PostgreSQL menulis:column "karyawan.nama" must appear in the GROUP BY clause or be used in an aggregate function. - Alias di HAVING.
HAVING jumlah > 2jalan di SQLite dan MySQL, tapi PostgreSQL menolak (column "jumlah" does not exist).HAVING COUNT(*) > 2aman di semuanya. - Urutan kelompok. SQLite kebetulan mengembalikan kelompok urut abjad (HR, IT, Marketing). Itu gak dijamin, jadi tulis
ORDER BYkalau urutannya penting.
⚠️ Syarat salah tempat, kolom nyasar
- Fungsi agregat di WHERE. Ditolak:
misuse of aggregate: COUNT(). Pindahkan keHAVING. - HAVING tanpa GROUP BY. Seluruh tabel jadi satu kelompok. Query latihan tanpa
GROUP BYtapi denganHAVING COUNT(*) > 2memberi 1 baris (IT | 8), bukan 2. - Kolom di SELECT yang gak ada di GROUP BY. SQLite diam saja dan mengisi nilai acak dari kelompoknya. Database lain menolak.
- Urutan klausa.
GROUP BYsetelahWHERE,HAVINGsetelahGROUP BY,ORDER BYdi belakangnya.
TL;DR: GROUP BY kolom bikin satu kelompok untuk tiap nilai yang berbeda, dan fungsi agregat menghitung per kelompok: hasilnya satu baris per kelompok. WHERE menyaring baris sebelum dikelompokkan, HAVING menyaring kelompok sesudahnya, dan syarat dengan COUNT, AVG dan kawan-kawan selalu di HAVING. Kolom biasa di SELECT harus ada di GROUP BY.
⚡ Satu baris per kelompok
Tulis:
SELECT departemen, COUNT(*) AS jumlah, AVG(gaji) AS rata_gaji
FROM karyawan
GROUP BY departemen;
Artinya: Tiap nilai departemen yang berbeda jadi satu kelompok: IT, HR, Marketing. COUNT dan AVG dihitung di dalam tiap kelompok, jadi hasilnya 3 baris.
⚡ HAVING: saring kelompok
Tulis:
SELECT departemen, COUNT(*) AS jumlah, AVG(gaji) AS rata_gaji
FROM karyawan
GROUP BY departemen
HAVING COUNT(*) > 2;
Artinya: HAVING ngecek tiap kelompok sesudah dihitung. Marketing cuma punya 2 orang, jadi dibuang.
⚡ WHERE atau HAVING
Tebak dulu: WHERE COUNT(*) > 2 ditulis sebelum GROUP BY. Jalan gak?
Ditolak: misuse of aggregate: COUNT(). Waktu WHERE jalan, kelompoknya belum ada, jadi COUNT(*) belum bisa dihitung.
Artinya: WHERE menyaring baris sebelum dikelompokkan, HAVING menyaring kelompok sesudahnya. Syarat dengan fungsi agregat selalu ditulis di HAVING.
⚡ Kolom di SELECT
Artinya: Kolom biasa di SELECT harus ada di GROUP BY, dan kolom lain dibungkus fungsi agregat. SQLite diam saja kalau aturan ini dilanggar, tapi MySQL dan PostgreSQL menolak.