Pengelompokan dan agregasi, tanpa hitung ganda

GROUP BY meringkas banyak baris menjadi satu baris per kombinasi berbeda dari ekspresi pengelompoknya. Semua hal lain tentang dia mengikuti dari kalimat itu — termasuk error-nya.

SELECT country, COUNT(*) AS customers
FROM customer
GROUP BY country;

Satu baris per negara. Artinya: begitu kamu mengelompokkan, satu baris keluaran mewakili banyak baris masukan, jadi yang boleh kamu pilih hanyalah ekspresi pengelompoknya sendiri dan agregat atas sisanya. Tidak ada jawaban yang masuk akal untuk “email yang mana?” ketika grupnya punya 400 buah.

Urutan evaluasi klausa

Hampir semua pertanyaan agregasi yang membingungkan terjawab dengan mengetahui urutan ini:

FROM  →  WHERE  →  GROUP BY  →  HAVING  →  SELECT  →  ORDER BY  →  LIMIT

Akibat yang pantas disebut:

• WHERE berjalan sebelum pengelompokan, jadi dia menyaring baris dan tidak bisa melihat agregat.
• HAVING berjalan setelah pengelompokan, jadi dia menyaring grup dan bisa melihat agregat.
• SELECT berjalan setelah keduanya, dan itulah kenapa alias yang didefinisikan di SELECT tidak tersedia di WHERE.
• ORDER BY berjalan terakhir, dan itulah kenapa alias tersedia di sana.

SELECT country, COUNT(*) AS customers
FROM customer
WHERE created_at >= '2026-01-01'     -- baris: sebelum pengelompokan
GROUP BY country
HAVING COUNT(*) >= 10                -- grup: setelah pengelompokan
ORDER BY customers DESC;             -- alias boleh di sini

Menaruh COUNT(*) >= 10 di WHERE adalah error di kedua mesin. Menaruh created_at >= '2026-01-01' di HAVING kadang jalan dan selalu salah: kamu menyaring setelah mengagregasi, jadi hitungannya sudah dihitung atas baris yang sebenarnya mau kamu buang.

Keterlihatan alias adalah satu dari sedikit tempat MySQL justru lebih longgar:

Jadi HAVING customers >= 10 jalan di MySQL dan gagal di PostgreSQL. Tulis ekspresi penuhnya dan dia jalan di keduanya.

ONLY_FULL_GROUP_BY

PostgreSQL sejak dulu menolak pemilihan kolom yang tidak dikelompokkan dan tidak diagregasi:

-- PostgreSQL: ERROR, column "customer.email" must appear in the GROUP BY clause
SELECT country, email, COUNT(*) FROM customer GROUP BY country;

MySQL dulu menerimanya dan mengembalikan email sembarang dari setiap grup — tanpa error, tanpa peringatan, dengan nilai yang bisa berubah antar eksekusi. Sejak 5.7 mode ONLY_FULL_GROUP_BY aktif secara bawaan dan MySQL ikut menolak, tapi mode itu setelan server, jadi server lama atau yang salah konfigurasi masih menerimanya.

Periksa sebelum memercayainya:

SELECT @@sql_mode;   -- MySQL: cari ONLY_FULL_GROUP_BY

Kalau kamu benar-benar ingin “satu baris perwakilan per grup”, sebutkan yang mana. Itu window function, bukan GROUP BY — materi 6.

MySQL memang menyediakan ANY_VALUE(col) untuk menyatakan terang-terangan “saya tahu ini sembarang”. Itu dokumentasi kompromi yang jujur; bukan perbaikan.

Agregasi bersyarat

Menghitung beberapa himpunan bagian dalam satu lintasan adalah teknik agregasi paling berguna yang ada, dan dialeknya berbeda.

-- PostgreSQL: klausa FILTER
SELECT
  COUNT(*)                                  AS orders,
  COUNT(*) FILTER (WHERE status = 'paid')   AS paid,
  COUNT(*) FILTER (WHERE shipped_at IS NULL) AS unshipped,
  SUM(unit_cents) FILTER (WHERE quantity > 1) AS bulk_cents
FROM "order" o JOIN order_item i ON i.order_id = o.id;

-- MySQL: SUM atas boolean, yang jalan di mana saja
SELECT
  COUNT(*)                                       AS orders,
  SUM(status = 'paid')                           AS paid,
  SUM(shipped_at IS NULL)                        AS unshipped,
  SUM(CASE WHEN quantity > 1 THEN unit_cents END) AS bulk_cents
FROM `order` o JOIN order_item i ON i.order_id = o.id;

SUM(boolean) jalan di MySQL karena boolean di sana bernilai 1 atau 0. Di PostgreSQL boolean bukan angka, jadi bentuk portabelnya adalah SUM(CASE WHEN cond THEN 1 ELSE 0 END) atau COUNT(*) FILTER (...).

Perhatikan CASE tanpa ELSE di baris terakhir — di sini disengaja. Baris yang gagal syaratnya menghasilkan NULL, dan SUM mengabaikan NULL, jadi totalnya hanya atas baris yang cocok. Ini satu-satunya tempat di mana ELSE yang hilang dari materi 2 adalah pilihan yang benar, dan di kode nyata itu pantas diberi komentar.

Ragam COUNT

SELECT
  COUNT(*)                    AS rows_seen,
  COUNT(coupon_code)          AS with_coupon,      -- hanya yang bukan NULL
  COUNT(DISTINCT coupon_code) AS distinct_coupons  -- bukan NULL, tanpa duplikat
FROM "order";

COUNT(DISTINCT ...) jauh lebih mahal daripada COUNT(*) — dia harus membuang duplikat, artinya mengurutkan atau meng-hash nilainya. Di tabel besar dia sering jadi bagian paling lambat dari satu query dasbor.

Mengagregasi string

Pemotongan di MySQL itu jebakannya: GROUP_CONCAT diam-diam memotong hasil pada group_concat_max_len dan hanya memunculkan warning, yang dibuang oleh sebagian besar klien. Laporan yang tampak baik di grup kecil kehilangan data tanpa suara di grup besar.

Bug yang penting: fan-out join

Ini bug angka-salah-di-laporan yang paling umum di SQL, dan dia bukan masalah NULL atau sintaks. Dia aritmetika.

Sebuah order punya satu biaya kirim dan banyak item. Join keduanya dan baris order-nya diulang sekali per item:

-- SALAH: biaya kirim dihitung sekali per item
SELECT o.id, SUM(i.unit_cents * i.quantity) AS goods, SUM(o.shipping_cents) AS shipping
FROM "order" o
JOIN order_item i ON i.order_id = o.id
GROUP BY o.id;

Order dengan tiga item melaporkan biaya kirim tiga kali. COUNT(*) punya masalah yang sama: dia menghitung baris item, bukan order.

Dua bentuk yang benar:

-- 1. Agregasi dulu, baru join. Satu baris per order di kedua sisi.
SELECT o.id, o.shipping_cents, g.goods
FROM "order" o
JOIN (
  SELECT order_id, SUM(unit_cents * quantity) AS goods
  FROM order_item
  GROUP BY order_id
) g ON g.order_id = o.id;

-- 2. Pertahankan join, tapi buat setiap agregat menghitung hal yang benar
SELECT o.id,
       SUM(i.unit_cents * i.quantity) AS goods,
       MIN(o.shipping_cents)          AS shipping,   -- konstan di dalam grup
       COUNT(DISTINCT o.id)           AS orders
FROM "order" o
JOIN order_item i ON i.order_id = o.id
GROUP BY o.id;

Pilihan 1 lebih jelas dan biasanya lebih cepat. Pilihan 2 jalan karena shipping_cents konstan di dalam satu grup, jadi MIN mengambilnya tanpa merusak apa pun — tapi dia bergantung pada pembaca yang tahu itu, dan karena itulah komentarnya ada.

Aturan umumnya: kalau sebuah query men-join satu-ke-banyak lalu mengagregasi, periksa apakah setiap agregat menjumlahkan pada tingkat butir yang benar. Menambahkan DISTINCT supaya angkanya terlihat benar biasanya menyembunyikan fan-out, bukan memperbaikinya.

Rollup dan subtotal

-- PostgreSQL: dukungan penuh
SELECT country, status, COUNT(*)
FROM "order" o JOIN customer c ON c.id = o.customer_id
GROUP BY GROUPING SETS ((country, status), (country), ());

-- Juga: ROLLUP (country, status), CUBE (country, status)

-- MySQL: hanya ROLLUP, dan ditulis sebagai pengubah
SELECT country, status, COUNT(*)
FROM `order` o JOIN customer c ON c.id = o.customer_id
GROUP BY country, status WITH ROLLUP;

Baris subtotal membawa NULL di kolom yang diagregasinya, dan itu tidak bisa dibedakan dari NULL sungguhan di data. GROUPING(country) mengembalikan 1 untuk baris subtotal di kedua mesin — pakai itu daripada menebak.

Yang perlu dibawa pulang

• FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Sebagian besar kebingungan larut kalau dicocokkan ke daftar itu.
• WHERE menyaring baris, HAVING menyaring grup. Keduanya tidak bisa ditukar.
• Tulis ekspresi penuh di HAVING, bukan aliasnya, supaya jalan di kedua mesin.
• Agregasi bersyarat: FILTER di PostgreSQL, SUM(CASE WHEN ...) di mana saja.
• GROUP_CONCAT memotong pada 1024 byte secara bawaan.
• Men-join satu-ke-banyak sebelum mengagregasi menghasilkan hitung ganda. Agregasi dulu.