GROUP BY dan HAVING — Agregat per Grup

Data yang dipakai di artikel ini — tabel score

Di artikel sebelumnya kamu menciutkan seluruh tabel menjadi satu nilai. `GROUP BY` di artikel ini (pengelompokan — mengumpulkan baris yang punya nilai sama ke dalam satu grup) menjalankan agregat per grup dan mengembalikan satu baris agregat per grup. Selain itu, `HAVING` memungkinkan kamu menyaring hasil agregat, seperti "hanya grup yang rata-ratanya 80 atau lebih".

Subjeknya adalah tabel score yang sama seperti sebelumnya (30 baris = 10 orang × 3 mata pelajaran). Mengelompokkan berdasarkan name menghasilkan 10 grup, dan mengelompokkan berdasarkan subject menghasilkan 3 grup.

Sebelum masuk ke latihan, lihat dulu definisi kolom dan contoh data pada tabel score.

① Gunakan PRAGMA table_info(score); untuk memeriksa nama kolom, tipe, dan kunci primer.

② Gunakan SELECT * FROM score LIMIT 5; untuk melihat pratinjau 5 baris data pertama.

GROUP BY — mengagregasi per grup

Saat kamu menulis GROUP BY kolom, baris yang punya nilai sama di kolom itu dikumpulkan ke dalam satu grup, dan fungsi agregat dihitung sekali per grup. Dengan SELECT name, AVG(score) FROM score GROUP BY name;, 3 baris dengan nama yang sama (satu per mata pelajaran) membentuk satu grup, dan kamu mendapat satu baris rata-rata per nama. Satu-satunya "kolom biasa" yang bisa kamu taruh di SELECT adalah kolom yang disebut di `GROUP BY`; kolom lain harus dibungkus dalam fungsi agregat (karena nilainya tidak tunggal di dalam satu grup).

-- Jumlah / rata-rata / nilai tertinggi per mata pelajaran (3 grup per subject)
SELECT
  subject,
  COUNT(*)             AS row_count,
  ROUND(AVG(score), 1) AS avg_score,
  MAX(score)           AS top_score
FROM score
GROUP BY subject;

Bayangkan kebutuhan "saya ingin daftar rata-rata nilai setiap siswa". (Jalankan dengan benar maka penjelasan akan muncul.)

① Kelompokkan tabel score berdasarkan nama.

② Untuk setiap grup, ambil nama sebagai name, jumlah mata pelajaran yang ditempuh sebagai subjects, dan rata-rata nilai sebagai avg_score, dalam urutan itu.

③ Bulatkan rata-rata ke 2 angka desimal. Urutan tidak ditentukan.

HAVING — menyaring hasil agregat

Setelah agregat grup, kadang kamu ingin menyaring berdasarkan nilai agregat, seperti "hanya tampilkan grup yang rata-ratanya 80 atau lebih". Kamu tidak bisa melakukan ini dengan WHERE. WHERE adalah klausa yang menilai baris individual sebelum pengelompokan, jadi agregat grup seperti AVG(score) belum dihitung. Di situlah `HAVING` berperan. HAVING ditulis setelah GROUP BY dan menilai kondisinya terhadap hasil agregat grup. Menulis HAVING AVG(score) >= 80 hanya menyisakan grup yang rata-ratanya 80 atau lebih.

-- Hanya nama yang rata-ratanya melebihi 85 (HAVING menilai nilai agregat)
SELECT
  name,
  ROUND(AVG(score), 2) AS avg_score
FROM score
GROUP BY name
HAVING AVG(score) > 85;

Bayangkan kebutuhan "saya ingin mendaftar hanya siswa berprestasi dengan rata-rata nilai 80 atau lebih".

① Kelompokkan tabel score berdasarkan nama.

② Untuk setiap grup, ambil nama sebagai name dan rata-rata nilai sebagai avg_score (dibulatkan ke 2 angka desimal).

③ Sisakan hanya grup yang rata-rata nilainya 80 atau lebih. Urutan tidak ditentukan.

WHERE vs. HAVING — menyaring baris, atau menyaring grup

WHERE dan HAVING keduanya penyaring, tetapi mereka bekerja pada waktu yang berbeda. WHERE menilai baris individual dan membuangnya sebelum pengelompokan. HAVING menilai per grup dan membuangnya setelah pengelompokan dan agregasi. Urutan eksekusinya adalah FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.

Kamu juga bisa memakai keduanya sekaligus. Dalam kasus itu alurnya adalah "persempit baris target dengan WHERE, lalu kelompokkan, lalu saring hasil agregat dengan HAVING". Misalnya, untuk mendapat "orang yang rata-rata per namanya 80 atau lebih, hanya mempertimbangkan Math dan English", kamu mempersempit baris dengan WHERE subject IN ('Math','English') dan mempersempit grup dengan HAVING AVG(score) >= 80.

-- Pakai WHERE dan HAVING bersama
-- Kecualikan Science, sisakan hanya nama yang rata-ratanya 80 atau lebih
SELECT
  name,
  ROUND(AVG(score), 2) AS avg_two
FROM score
WHERE subject <> 'Science'
GROUP BY name
HAVING AVG(score) >= 80;

Bayangkan kebutuhan "hanya mempertimbangkan dua mata pelajaran Math dan English, saya ingin orang yang rata-rata per namanya 85 atau lebih".

① Dari tabel score, sisakan hanya baris yang subject-nya Math atau English (Science di luar cakupan).

② Kelompokkan baris yang tersisa berdasarkan nama dan ambil nama sebagai name dan rata-rata nilai dua mata pelajaran sebagai avg_two (dibulatkan ke 2 angka desimal).

③ Lalu sisakan hanya grup yang rata-ratanya 85 atau lebih. Urutan tidak ditentukan.

Bayangkan kebutuhan "saya ingin menampilkan rata-rata per mata pelajaran sebagai peringkat, tertinggi dulu".

① Kelompokkan tabel score berdasarkan mata pelajaran.

② Untuk setiap grup, ambil mata pelajaran sebagai subject, jumlah siswa yang menempuhnya sebagai students, dan rata-rata nilai sebagai avg_score (dibulatkan ke 2 angka desimal), dalam urutan itu.

③ Urutkan mereka dari rata-rata tertinggi ke terendah.