Hari 12: GROUP BY — Agregasi per Kelompok

GROUP BY — Kelompokkan dan Ringkas

GROUP BY membagi baris menjadi kelompok-kelompok, lalu fungsi agregat dijalankan per kelompok.

-- Berapa pelanggan di setiap kota?
SELECT kota, COUNT(*) AS jumlah_pelanggan
FROM pelanggan
GROUP BY kota;

-- Total stok per kategori
SELECT kategori_id, SUM(stok) AS total_stok
FROM produk
GROUP BY kategori_id;

-- Rata-rata harga per kategori
SELECT kategori_id,
  COUNT(*) AS jumlah_produk,
  ROUND(AVG(harga), 0) AS rata_harga
FROM produk
GROUP BY kategori_id
ORDER BY rata_harga DESC;

⚠️ Aturan GROUP BY: setiap kolom di SELECT yang bukan fungsi agregat, harus ada di GROUP BY.

-- Multi kolom GROUP BY
SELECT kota, status, COUNT(*) AS jumlah
FROM pelanggan
GROUP BY kota, status
ORDER BY kota, status;