KAL
Menu

Agregat dan GROUP BY

COUNT, SUM, dan AVG beserta semantik pengelompokan yang benar pada MySQL 8.

Daftar isi

Topik ini membahas fungsi agregat untuk meringkas banyak baris menjadi satu nilai ringkas. Pembahasan mencakup fungsi COUNT, SUM, dan AVG, klausa GROUP BY untuk membentuk kelompok, serta aturan kolom pada mode ONLY_FULL_GROUP_BY yang mencegah hasil pengelompokan menjadi ambigu. Basis data latihan berupa rekap nilai ujian dibangun ulang dari berkas skrip, sehingga setiap keluaran pada contoh dapat diulang dari data yang sama.

Menyiapkan basis data latihan#

Basis data latihan nilai_ujian dibangun ulang dari berkas skrip agar seluruh contoh pada topik ini bermula dari data yang sama. Berkas data-nilai-ujian.sql memuat pembuatan ulang basis data, tabel ujian, dan isi berupa dua belas baris data. Setiap baris mewakili satu siswa pada satu kelas, yaitu kelas 10A, 10B, dan 11A dengan empat siswa per kelas. Nilai ujian berada pada rentang 65 sampai 93 dan sengaja dipilih agar mudah dihitung ulang dengan tangan.

sql data-nilai-ujian.sql 
-- tidak-dijalankan
-- Berkas latihan topik agregat: membangun ulang basis data nilai_ujian dari nol.
-- Jalankan dengan: mysql -u root < data-nilai-ujian.sql

DROP DATABASE IF EXISTS nilai_ujian;
CREATE DATABASE nilai_ujian;
USE nilai_ujian;

CREATE TABLE ujian (
  id INT PRIMARY KEY,
  kelas VARCHAR(4) NOT NULL,
  siswa VARCHAR(30) NOT NULL,
  nilai TINYINT UNSIGNED NOT NULL
);

INSERT INTO ujian (id, kelas, siswa, nilai) VALUES
  (1,  '10A', 'Ali Rahman',    88),
  (2,  '10A', 'Budi Santoso',  74),
  (3,  '10A', 'Citra Lestari', 91),
  (4,  '10A', 'Dewi Anggraini', 79),
  (5,  '10B', 'Eka Putri',     65),
  (6,  '10B', 'Fajar Nugroho', 82),
  (7,  '10B', 'Gita Wulandari', 77),
  (8,  '10B', 'Hadi Pratama',  70),
  (9,  '11A', 'Intan Maharani', 85),
  (10, '11A', 'Joko Prasetyo', 68),
  (11, '11A', 'Kartika Sari',  93),
  (12, '11A', 'Lutfi Hakim',   80);
bash 
# membangun ulang basis data latihan dari berkas skrip
mysql -u root < data-nilai-ujian.sql

Perintah di atas tidak mencetak apa pun karena skripnya hanya berisi pernyataan pembuatan struktur dan pengisian data. Tabel ujian kini berisi kolom id, kelas, siswa, dan nilai, serta dua belas baris yang tersebar merata pada tiga kelas. Rincian pernyataan CREATE TABLE beserta pilihan tipe datanya dibahas pada topik tersendiri di jalur ini, sehingga bagian ini hanya memakai data yang sudah disediakan. Apabila latihan diulang dari awal, jalankan kembali perintah yang sama, karena DROP DATABASE IF EXISTS pada awal berkas menjamin keadaan data selalu kembali ke titik yang sama. Seluruh query pada bagian berikutnya dievaluasi terhadap data tersebut.

Menghitung baris dengan COUNT#

Fungsi COUNT menghitung banyaknya baris atau nilai pada satu kelompok data. Bentuk COUNT(*) menghitung seluruh baris tanpa melihat isinya, sedangkan bentuk COUNT(kolom) hanya menghitung baris yang kolomnya tidak NULL. Alias melalui kata kunci AS memberi nama pada kolom hasil sehingga keluaran lebih mudah dibaca. Fungsi ini paling sering dipakai untuk pertanyaan berhitung, misalnya banyaknya siswa dalam satu tabel atau banyaknya kelompok yang terbentuk. Penghitungannya dilakukan server atas seluruh baris yang lolos dari penyaringan, sehingga hasilnya selalu satu nilai tunggal.

bash 
# menghitung seluruh baris tabel ujian
mysql -t -u root nilai_ujian -e "SELECT COUNT(*) AS jumlah_siswa FROM ujian;"
text Keluaran 
+--------------+
| jumlah_siswa |
+--------------+
|           12 |
+--------------+

Perbedaan COUNT(*) dan COUNT(kolom) baru terlihat ketika data memuat NULL. Query berikut membentuk tiga baris sementara melalui tabel turunan, yaitu dua nilai terisi dan satu NULL, lalu menghitungnya dengan dua bentuk COUNT sekaligus.

bash 
# membandingkan COUNT(*) dan COUNT(kolom) pada data berisi NULL
mysql -t -u root nilai_ujian -e "SELECT COUNT(*) AS baris, COUNT(nilai) AS nilai_terisi, AVG(nilai) AS rata_rata FROM (SELECT 80 AS nilai UNION ALL SELECT NULL UNION ALL SELECT 90) AS coba;"
text Keluaran 
+-------+--------------+-----------+
| baris | nilai_terisi | rata_rata |
+-------+--------------+-----------+
|     3 |            2 |   85.0000 |
+-------+--------------+-----------+

COUNT(*) tetap menghasilkan 3 karena baris dengan NULL tetap ada, sedangkan COUNT(nilai) menghasilkan 2 karena hanya nilai terisi yang dihitung. Rata-rata 85.0000 memperlihatkan semantik yang sama, yaitu AVG mengabaikan NULL dan membagi jumlah nilai dengan banyaknya nilai terisi, bukan dengan banyaknya baris. Bentuk COUNT(DISTINCT kolom) menghitung nilai unik tanpa duplikat, sehingga COUNT(DISTINCT kelas) pada tabel ujian menghasilkan 3 sesuai banyaknya kelas yang ada.

bash 
# menghitung banyaknya kelas yang berbeda
mysql -t -u root nilai_ujian -e "SELECT COUNT(DISTINCT kelas) AS kelas_berbeda FROM ujian;"
text Keluaran 
+---------------+
| kelas_berbeda |
+---------------+
|             3 |
+---------------+

Menjumlahkan dan merata-ratakan dengan SUM dan AVG#

Fungsi SUM menjumlahkan seluruh nilai pada satu kolom, sedangkan AVG merata-ratakannya. Keduanya mengabaikan NULL, sebagaimana terlihat pada contoh tabel turunan sebelumnya. MIN dan MAX melengkapi keduanya dengan mengembalikan nilai terkecil dan terbesar, sehingga keempat fungsi tersebut cukup untuk menyusun rekap statistik satu kolom dalam satu query. Semuanya menerima nama kolom di dalam kurungnya dan mengevaluasi setiap baris yang lolos, sehingga hasilnya berupa satu nilai untuk seluruh tabel.

bash 
# merangkum kolom nilai dengan SUM, AVG, MIN, dan MAX
mysql -t -u root nilai_ujian -e "SELECT SUM(nilai) AS total, AVG(nilai) AS rata_rata, MIN(nilai) AS terendah, MAX(nilai) AS tertinggi FROM ujian;"
text Keluaran 
+-------+-----------+----------+-----------+
| total | rata_rata | terendah | tertinggi |
+-------+-----------+----------+-----------+
|   952 |   79.3333 |       65 |        93 |
+-------+-----------+----------+-----------+

Hasil AVG pada keluaran berupa 79.3333, yaitu 952 dibagi dua belas siswa. MySQL menghitung pembagian itu secara desimal penuh, lalu menyajikan empat angka di belakang koma untuk kolom bertipe bilangan bulat. Angka tersebut dengan demikian bukan hasil pembulatan yang dikerjakan server secara diam-diam. Apabila presisi tampilan tertentu diinginkan, bungkus hasilnya dengan ROUND(AVG(nilai), 1), dan bentuk itulah yang dipakai pada bagian berikutnya. Angka total 952 juga terverifikasi dengan cepat karena seluruh nilai pada data latihan dapat dijumlahkan secara manual.

Mengelompokkan hasil dengan GROUP BY#

Klausa GROUP BY membagi baris ke dalam kelompok berdasarkan nilai kolomnya, lalu setiap fungsi agregat dihitung per kelompok. Semantik pengelompokannya tegas, yaitu satu baris keluaran untuk setiap kombinasi nilai kolom GROUP BY yang berbeda, dan angka pada fungsi agregat hanya berasal dari baris-baris dalam kelompok itu.

sql Sintaks GROUP BY 
-- tidak-dijalankan
SELECT kolom_kelompok, AGREGAT(kolom_nilai) AS alias_hasil
FROM nama_tabel
GROUP BY kolom_kelompok
ORDER BY kolom_kelompok;
bash 
# merangkum nilai ujian per kelas
mysql -t -u root nilai_ujian -e "SELECT kelas, COUNT(*) AS jumlah, SUM(nilai) AS total, ROUND(AVG(nilai), 1) AS rata_rata FROM ujian GROUP BY kelas ORDER BY kelas;"
text Keluaran 
+-------+--------+-------+-----------+
| kelas | jumlah | total | rata_rata |
+-------+--------+-------+-----------+
| 10A   |      4 |   332 |      83.0 |
| 10B   |      4 |   294 |      73.5 |
| 11A   |      4 |   326 |      81.5 |
+-------+--------+-------+-----------+

Keluaran memuat tiga baris sesuai tiga kelas. Baris kelas 10A dihitung dari empat baris data dengan total 332 dan rata-rata 83.0, sedangkan dua baris lainnya dihitung dengan cara yang sama. Kolom kelas yang menjadi dasar pengelompokan muncul polos pada daftar SELECT, sedangkan kolom lain hanya boleh muncul di dalam fungsi agregat. ORDER BY kelas menegaskan urutan keluaran karena sejak MySQL 8.0 klausa GROUP BY tidak lagi mengurutkan hasil secara implisit. Kunci pemecah seri belum diperlukan di sini karena kolom kelas bernilai unik per kelompok, sedangkan pengelompokan yang bisa menghasilkan seri perlu kunci tambahan agar urutannya tetap. Pengelompokan juga dapat memakai dua kolom sekaligus, misalnya GROUP BY kelas, siswa, yang membentuk satu kelompok untuk setiap kombinasi keduanya.

Aturan kolom pada mode ONLY_FULL_GROUP_BY#

Server MySQL 8.0 menyalakan mode SQL ONLY_FULL_GROUP_BY secara default, sehingga query pengelompokan yang memuat kolom non-agregat di luar klausa GROUP BY ditolak. Aturan ini mencegah hasil yang tidak pasti, karena untuk satu kelompok terdapat banyak nilai kandidat dan server tidak memiliki dasar untuk memilih salah satunya. Pesan error berikut muncul ketika kolom siswa dimunculkan polos di samping pengelompokan per kelas.

bash 
# sengaja memunculkan kolom non-agregat di luar GROUP BY
mysql -t -u root nilai_ujian -e "SELECT kelas, siswa, COUNT(*) FROM ujian GROUP BY kelas;"
text Keluaran 
ERROR 1055 (42000) at line 1: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'nilai_ujian.ujian.siswa' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Pesan ERROR 1055 menunjuk ekspresi kedua pada daftar SELECT, yaitu kolom siswa, sebagai kolom non-agregat yang tidak tergantung secara fungsional (functionally dependent) pada kolom GROUP BY. Perbaikannya mengikuti maksud query. Apabila rekap per siswa yang diinginkan, masukkan siswa ke dalam GROUP BY sehingga kelompoknya berpindah ke kombinasi kelas dan siswa. Apabila satu nilai per kelas yang diinginkan, bungkus kolomnya dengan fungsi agregat, misalnya MIN(siswa) atau MAX(siswa).

bash 
# memasukkan siswa ke dalam GROUP BY
mysql -t -u root nilai_ujian -e "SELECT kelas, siswa, COUNT(*) AS jumlah FROM ujian GROUP BY kelas, siswa ORDER BY kelas, siswa;"
text Keluaran 
+-------+----------------+--------+
| kelas | siswa          | jumlah |
+-------+----------------+--------+
| 10A   | Ali Rahman     |      1 |
| 10A   | Budi Santoso   |      1 |
| 10A   | Citra Lestari  |      1 |
| 10A   | Dewi Anggraini |      1 |
| 10B   | Eka Putri      |      1 |
| 10B   | Fajar Nugroho  |      1 |
| 10B   | Gita Wulandari |      1 |
| 10B   | Hadi Pratama   |      1 |
| 11A   | Intan Maharani |      1 |
| 11A   | Joko Prasetyo  |      1 |
| 11A   | Kartika Sari   |      1 |
| 11A   | Lutfi Hakim    |      1 |
+-------+----------------+--------+

Keluaran kini memuat dua belas kelompok, satu untuk setiap siswa, dan setiap kelompok berisi satu baris. Sebagai kebalikan aturan itu, kolom yang tergantung secara fungsional pada kolom GROUP BY tetap boleh muncul polos. Contohnya SELECT id, kelas FROM ujian GROUP BY id sah karena id adalah kunci primer (primary key) sehingga setiap nilai id menentukan tepat satu nilai kelas.

Kesalahan umum#

Kesalahan pertama adalah mengandalkan urutan hasil GROUP BY. Sejak MySQL 8.0, urutan keluaran pengelompokan tidak dijamin, sehingga urutan tampilan harus dinyatakan dengan ORDER BY. Keluaran yang tampak terurut pada data kecil dapat berubah begitu struktur tabel, indeks, atau versi servernya berbeda. Perbaikannya ringkas, yaitu menambahkan ORDER BY dengan kolom yang diinginkan beserta kunci pemecah seri apabila nilai-nilainya bisa sama.

Kesalahan kedua adalah memilih bentuk COUNT tanpa memeriksa adanya NULL. COUNT(*) dan COUNT(kolom) menghasilkan angka berbeda pada kolom yang memuat NULL, dan AVG mengikuti perilaku COUNT(kolom) karena pembaginya adalah banyaknya nilai terisi. Pada tabel yang dijamin tanpa NULL, keduanya tetap berbeda makna, sehingga kebiasaan memilih bentuk yang tepat sejak awal mencegah keliru ketika datanya berubah. Tentukan dahulu makna angka yang diharapkan, lalu pilih bentuk yang sesuai.

Kesalahan ketiga adalah menganggap hasil AVG sudah dibulatkan oleh server. Pembagiannya dilakukan penuh dan disajikan sesuai skala tipe datanya, misalnya empat angka di belakang koma untuk kolom bilangan bulat. Gunakan ROUND secara eksplisit apabila presentasinya memang menghendaki pembulatan. Keluaran numerik yang panjang bukan keanehan data, melainkan cara server menyajikan hasil desimalnya secara penuh.

Ringkasan#

  • COUNT(*) menghitung seluruh baris, COUNT(kolom) hanya menghitung nilai yang terisi, dan COUNT(DISTINCT kolom) menghitung nilai unik.
  • SUM dan AVG mengabaikan NULL, sehingga pembagi AVG adalah banyaknya nilai terisi, bukan banyaknya baris.
  • GROUP BY membentuk satu baris keluaran per kelompok, dan setiap fungsi agregat dihitung hanya dari baris kelompoknya.
  • Mode ONLY_FULL_GROUP_BY menolak kolom non-agregat di luar GROUP BY, kecuali kolom itu tergantung secara fungsional pada kolom pengelompokan.

Referensi#