KAL
Menu

GROUP BY

Mengelompokkan baris sebelum diagregasi dan memahami aturan ketat kolom pada SELECT.

Daftar isi

Topik ini membahas GROUP BY, klausa yang mengelompokkan baris menurut nilai yang sama sebelum agregasi dijalankan, beserta aturan ketat PostgreSQL mengenai kolom yang boleh muncul pada SELECT. Aturan ini sering dirasakan ketat, tetapi justru melindungi angka laporan dari hasil yang tidak bermakna.

Blok penyiapan dataset#

bash 
# siapkan ulang tabel dan data contoh untuk topik ini
PGOPTIONS='-c client_min_messages=warning' psql -q -U postgres -d perpustakaan <<'SQL'
DROP TABLE IF EXISTS peminjaman, buku, anggota, kategori CASCADE;
CREATE TABLE kategori (id SERIAL PRIMARY KEY, nama TEXT NOT NULL UNIQUE);
CREATE TABLE anggota (id SERIAL PRIMARY KEY, nama TEXT NOT NULL, email TEXT UNIQUE, bergabung DATE NOT NULL, aktif BOOLEAN NOT NULL DEFAULT TRUE);
CREATE TABLE buku (id SERIAL PRIMARY KEY, judul TEXT NOT NULL, penulis TEXT NOT NULL, tahun INTEGER NOT NULL, kategori_id INTEGER REFERENCES kategori(id));
CREATE TABLE peminjaman (id SERIAL PRIMARY KEY, anggota_id INTEGER NOT NULL REFERENCES anggota(id), buku_id INTEGER NOT NULL REFERENCES buku(id), tanggal_pinjam DATE NOT NULL, tanggal_kembali DATE);
INSERT INTO kategori (nama) VALUES ('Pemrograman'), ('Sains'), ('Sejarah'), ('Fiksi'), ('Statistika');
INSERT INTO anggota (nama, email, bergabung, aktif) VALUES ('Ali Rahman', '[email protected]', '2024-01-15', TRUE), ('Budi Santoso', '[email protected]', '2024-03-02', TRUE), ('Citra Kirana', '[email protected]', '2024-05-20', FALSE), ('Dewi Lestari', '[email protected]', '2024-08-11', TRUE), ('Eka Pratama', NULL, '2025-01-30', TRUE);
INSERT INTO buku (judul, penulis, tahun, kategori_id) VALUES ('Dasar Basis Data', 'Sari Wulandari', 2021, 1), ('Jaringan Komputer', 'Ahmad Fauzi', 2019, 1), ('Fisika untuk Pemula', 'Rina Marlina', 2020, 2), ('Kimia Organik', 'Rina Marlina', 2022, 2), ('Sejarah Nusantara', 'Bambang Wijaya', 2018, 3), ('Cerita Kota Lama', 'Nur Aisyah', 2023, 4), ('Statistika Terapan', 'Sari Wulandari', 2022, 5), ('Algoritma dan Struktur Data', 'Ahmad Fauzi', 2021, 1);
INSERT INTO peminjaman (anggota_id, buku_id, tanggal_pinjam, tanggal_kembali) VALUES (1, 1, '2025-03-01', '2025-03-15'), (1, 7, '2025-03-05', '2025-03-20'), (2, 3, '2025-03-10', NULL), (3, 5, '2025-02-20', '2025-03-01'), (2, 1, '2025-04-02', NULL), (4, 6, '2025-04-07', NULL), (5, 8, '2025-04-09', '2025-04-25'), (4, 2, '2025-05-01', NULL);
SQL

Mengelompokkan sebelum merangkum#

GROUP BY menyatukan baris yang nilainya sama pada kolom kelompok, lalu setiap fungsi agregasi menghitung satu nilai per kelompok. Query berikut menghitung banyaknya buku pada setiap kategori, dengan nama kategori diambil melalui join.

bash 
# jumlah buku per kategori
psql -U postgres -d perpustakaan -c "SELECT k.nama AS kategori, COUNT(b.id) AS jumlah_buku FROM kategori k LEFT JOIN buku b ON b.kategori_id = k.id GROUP BY k.nama ORDER BY jumlah_buku DESC, k.nama;"
text Keluaran 
  kategori   | jumlah_buku
-------------+-------------
 Pemrograman |           3
 Sains       |           2
 Fiksi       |           1
 Sejarah     |           1
 Statistika  |           1
(5 rows)

LEFT JOIN dipakai agar kategori tanpa buku tetap muncul dengan hitungan nol, karena COUNT kolom buku mengabaikan NULL, dan pola perhitungan per kelompok semacam ini adalah laporan paling umum di basis data apa pun. Kolom kelompok boleh berupa ekspresi, sehingga pengelompokan menurut fungsi juga sah.

Aturan ketat kolom pada SELECT#

PostgreSQL menolak query yang menyebut kolom tanpa agregasi pada SELECT padahal kolom itu tidak ada di GROUP BY. Aturan ini berbeda dari perilaku basis data lain yang memilih nilai sembarang dari kelompoknya, dan pesan errornya menyebut kolom yang wajib masuk klausa GROUP BY atau diagregasi.

bash 
# penolakan kolom yang tidak diagregasi
psql -U postgres -d perpustakaan -c "SELECT penulis, judul FROM buku GROUP BY penulis;"
text Keluaran 
ERROR:  column "buku.judul" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT penulis, judul FROM buku GROUP BY penulis;
                        ^

Server menolak dengan jelas karena satu penulis memuat banyak judul, dan memilih salah satunya diam-diam berarti memutar isi data. Dua jalan keluarnya menambahkan kolom itu pada GROUP BY bila kelompoknya memang perlu lebih halus, atau membungkusnya dengan agregasi seperti MAX bila satu wakil memang diinginkan.

Mengelompokkan menurut ekspresi#

Kolom kelompok berupa ekspresi membuka pengelompokan yang tidak tersimpan sebagai kolom, misalnya abjad awal judul atau tahun terbit tunggal. Ekspresinya ditulis identik pada GROUP BY dan SELECT, atau diberi alias yang dirujuk pada GROUP BY.

bash 
# kelompok menurut penulis dengan hitungan dan rentang tahun
psql -U postgres -d perpustakaan -c "SELECT penulis, COUNT(*) AS karya, MIN(tahun) AS mulai, MAX(tahun) AS terakhir FROM buku GROUP BY penulis ORDER BY karya DESC, penulis;"
text Keluaran 
    penulis     | karya | mulai | terakhir
----------------+-------+-------+----------
 Ahmad Fauzi    |     2 |  2019 |     2021
 Rina Marlina   |     2 |  2020 |     2022
 Sari Wulandari |     2 |  2021 |     2022
 Bambang Wijaya |     1 |  2018 |     2018
 Nur Aisyah     |     1 |  2023 |     2023
(5 rows)

Tiga penulis dengan dua karya muncul di atas, dan rentang tahunnya memberi konteks yang tidak diberi oleh hitungan tunggal. Satu query menghitung banyak agregat sekaligus atas kelompok yang sama, tanpa membaca tabel berulang kali.

Urutan klausa pada query#

Klausa SQL dinilai dengan urutan logis yang tetap, yaitu FROM, WHERE, GROUP BY, HAVING, SELECT, dan ORDER BY. Urutan inilah alasan alias pada SELECT dapat dirujuk GROUP BY dan ORDER BY tetapi tidak pada WHERE, dan memahami urutan ini menghapus sebagian besar kebingungan kesalahan kolom.

WHERE dinilai sebelum pengelompokan sehingga menyaring baris, sedangkan HAVING dinilai sesudahnya dan menyaring kelompok, pembagian yang menjadi topik penuh pada bagian berikutnya. Kesalahan menempatkan syarat di sisi yang salah adalah kesalahan paling umum pada query agregasi.

Kesalahan umum#

Kesalahan pertama adalah menyebut kolom non-agregat di luar GROUP BY, yang ditolak PostgreSQL dan diperbaiki dengan menegaskan maksud, bukan dengan mencari cara memaksa. Kesalahan kedua adalah mengira GROUP BY menyaring baris, padahal tugasnya mengelompokkan, dan penyaringan baris tetap tugas WHERE.

Kesalahan ketiga adalah mengelompokkan menurut kolom yang salah satunya tidak stabil, misalnya nama bebas yang ditulis beragam, sehingga kelompok terpecah demi beda kapitalisasi, dan normalisasi data lebih dahulu menjadi solusinya. Kesalahan keempat adalah lupa ORDER BY pada laporan per kelompok, karena urutan kelompok tanpa deklarasi tidak dijanjikan, sebagaimana dibahas pada modul psql dan query pertama.

Ringkasan#

  • GROUP BY menyatukan baris bernilai sama, lalu setiap agregasi menghasilkan satu nilai per kelompok.
  • PostgreSQL menolak kolom non-agregat di luar GROUP BY, dan dua jalan sahnya masuk kelompok atau diagregasi.
  • Kolom kelompok boleh berupa ekspresi, dan satu query menghitung banyak agregat per kelompok sekaligus.
  • Urutan logis FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY menjelaskan di mana alias boleh dirujuk.

Referensi#