GROUP BY dan HAVING

Idenya

"Berapa pendapatan kita?" adalah satu angka. "Berapa pendapatan kita per kategori?" adalah sebuah tabel kecil: satu angka untuk setiap kategori. Itulah bentuk pertanyaan bisnis yang paling umum, dan GROUP BY menjawabnya.

GROUP BY pertamamu

Mulai dari versi sederhana pertanyaannya: berapa banyak produk yang dijual toko di setiap kategori? Kamu bisa menjalankan lima query terpisah, satu WHERE category = ... per kategori, tapi kamu harus tahu semua kategorinya lebih dulu, dan kategori baru akan terlewat. GROUP BY mencarikannya untukmu:

SELECT category, COUNT(*) AS products
FROM products
GROUP BY category
ORDER BY category;

 category    | products
-------------+----------
 Coffee      |        3
 Electronics |        3
 Home        |        3
 Kitchen     |        3
 Stationery  |        3
(5 rows)

Inilah yang terjadi:

1. FROM products membuka ke-15 baris.
2. GROUP BY category membuat satu tumpukan per kategori.
3. SELECT category, COUNT(*) menghasilkan satu baris per tumpukan: kategori tumpukan itu dan berapa banyak barisnya.

Agregat apa pun bekerja per kelompok:

SELECT department,
       COUNT(*) AS headcount,
       ROUND(AVG(salary)) AS avg_salary,
       MAX(salary) AS top_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

 department | headcount | avg_salary | top_salary
------------+-----------+------------+------------
 Management |         1 |   45000000 |   45000000
 Data       |         2 |   12750000 |   13000000
 Sales      |         5 |   11800000 |   22000000
 Operations |         4 |   10625000 |   21000000
(4 rows)

Aturan emas

Ingat error dari modul sebelumnya, SELECT name, MAX(price) FROM products? Dengan GROUP BY, aturannya menjadi presisi:

category tidak masalah (satu nilai per tumpukan kategori). COUNT(*) tidak masalah (satu hitungan per tumpukan). name saja tidak bisa: satu tumpukan punya beberapa nama.

Mengelompokkan berdasarkan beberapa kolom

Kadang satu cara membagi data tidak cukup. Tim operasional ingin tahu apakah pembatalan lebih sering terjadi di website atau di aplikasi. Untuk itu pesanan harus dibagi berdasarkan status dan channel sekaligus. Kelompokkan berdasarkan dua kolom dan kamu mendapat satu baris per kombinasi keduanya:

SELECT status, channel, COUNT(*) AS orders
FROM orders
GROUP BY status, channel
ORDER BY status, channel;

 status    | channel | orders
-----------+---------+--------
 cancelled | app     |      2
 cancelled | web     |      8
 completed | app     |     10
 completed | web     |     16
 pending   | web     |      1
 shipped   | web     |      3
(6 rows)

Total per pesanan

Pengelompokan juga merupakan cara beralih dari baris-baris rinci ke total. Pesanan berisi kopi, mug, dan ketel disimpan sebagai tiga baris di order_items, satu per produk, dan tidak ada tabel yang menyimpan total pesanannya. Kelompokkan baris-baris itu berdasarkan order_id, dan SUM menjumlahkan baris setiap pesanan menjadi totalnya:

SELECT order_id,
       COUNT(*) AS lines,
       SUM(quantity * unit_price) AS order_total
FROM order_items
GROUP BY order_id
ORDER BY order_total DESC
LIMIT 5;

 order_id | lines | order_total
----------+-------+-------------
       32 |     3 |     1070000
       17 |     2 |     1024000
        3 |     1 |      960000
        8 |     3 |      918000
       26 |     1 |      867000
(5 rows)

HAVING: memfilter kelompok

Pesanan mana yang nilainya di atas Rp 1.000.000? Kamu tidak bisa memakai WHERE untuk itu: WHERE berjalan sebelum pengelompokan, saat totalnya belum ada. Coba dan PostgreSQL akan menghentikanmu:

SELECT order_id, SUM(quantity * unit_price) AS order_total
FROM order_items
WHERE SUM(quantity * unit_price) > 1000000
GROUP BY order_id;

ERROR: aggregate functions are not allowed in WHERE

HAVING adalah filter yang berjalan setelah pengelompokan, pada kelompok-kelompoknya:

SELECT order_id, SUM(quantity * unit_price) AS order_total
FROM order_items
GROUP BY order_id
HAVING SUM(quantity * unit_price) > 1000000
ORDER BY order_total DESC;

 order_id | order_total
----------+-------------
       32 |     1070000
       17 |     1024000
(2 rows)

WHERE dan HAVING bersama-sama

Keduanya melakukan pekerjaan yang berbeda, dan kamu akan sering memakai keduanya:

• WHERE memfilter baris sebelum pengelompokan: "hanya pesanan completed".
• HAVING memfilter kelompok setelah pengelompokan: "hanya pelanggan dengan minimal 3 pesanan seperti itu".

SELECT customer_id, COUNT(*) AS completed_orders
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING COUNT(*) >= 3
ORDER BY completed_orders DESC, customer_id;

 customer_id | completed_orders
-------------+------------------
          11 |                6
          12 |                5
           8 |                4
           5 |                3
(4 rows)

Urutan eksekusi lengkapnya sekarang:

Mengapa ini penting untuk kode bersih

Sebagian besar dashboard adalah tumpukan query GROUP BY. Kebiasaan yang menjaganya tetap benar: kelompokkan berdasarkan persis kolom-kolom yang kamu tampilkan, filter baris di WHERE dan kelompok di HAVING, dan beri nama setiap agregat. Saat sebuah angka terlihat salah, tanyakan "apa itu satu baris dari hasilku?" ("satu baris per kategori", "satu baris per pesanan"). Kalau kamu tidak bisa menjawabnya dalam satu kalimat, query-nya perlu dilihat lagi.

Rangkuman

• GROUP BY col membuat satu baris output per nilai berbeda (atau kombinasi nilai).
• Setiap kolom SELECT harus dikelompokkan atau diagregasi.
• WHERE memfilter baris sebelum pengelompokan; HAVING memfilter kelompok setelahnya.
• Agregat tidak boleh ada di WHERE.
• Urutan eksekusi: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.