Fungsi Agregasi & Grouping
Fungsi agregasi memungkinkan kita merangkum data: menghitung total, rata-rata, nilai tertinggi, dan sebagainya. Artikel ini membahas COUNT, SUM, AVG, MIN, MAX, serta GROUP BY dan HAVING untuk analisis data per kelompok.
Pendahuluan
Sampai sekarang kita selalu menampilkan data baris per baris. Namun seringkali kita butuh ringkasan: berapa total penjualan bulan ini? Berapa rata-rata harga produk? Produk mana yang paling mahal? Pertanyaan-pertanyaan ini dijawab oleh fungsi agregasi.
Artikel ini membahas lima fungsi agregasi utama (COUNT, SUM, AVG, MIN, MAX), cara mengelompokkan data dengan GROUP BY, dan cara memfilter hasil grup dengan HAVING.
Asumsi: Anda berada di database
toko_online(USE toko_online;) dan sudah mengisi data contoh pada tabelproduk,pelanggan,pesanan, dandetail_pesanan.
Fungsi Agregasi Dasar
Fungsi agregasi menerima banyak baris dan mengembalikan satu nilai ringkasan.
COUNT — Menghitung Jumlah Baris
Menghitung semua baris:
SELECT COUNT(*) AS total_produk FROM produk;
Menghitung baris dengan kolom tertentu tidak NULL:
SELECT COUNT(telepon) AS pelanggan_punya_telepon FROM pelanggan;
Perbedaan penting:
COUNT(*)— menghitung semua baris, termasuk yang memilikiNULL.COUNT(kolom)— menghitung baris yang kolomnya tidakNULL.
Menghitung nilai unik:
SELECT COUNT(DISTINCT status) AS jumlah_status_berbeda FROM pesanan;
Menghitung dengan kondisi (menggunakan CASE):
SELECT
COUNT(*) AS total_pesanan,
COUNT(CASE WHEN status = 'selesai' THEN 1 END) AS pesanan_selesai,
COUNT(CASE WHEN status = 'baru' THEN 1 END) AS pesanan_baru
FROM pesanan;
SUM — Menjumlahkan Nilai
SELECT SUM(total) AS total_penjualan FROM pesanan;
Dengan kondisi:
SELECT SUM(total) AS total_penjualan_selesai
FROM pesanan
WHERE status = 'selesai';
Catatan:
SUMmengabaikan nilaiNULL. Jika semua barisNULL, hasilnyaNULL(bukan 0). GunakanIFNULL(SUM(kolom), 0)jika ingin hasil 0.
AVG — Rata-rata
SELECT AVG(harga) AS rata_rata_harga FROM produk;
AVG(kolom) setara dengan SUM(kolom) / COUNT(kolom). Karena COUNT(kolom) mengabaikan NULL, AVG juga mengabaikan NULL.
Rata-rata dengan pembulatan:
SELECT ROUND(AVG(harga), 2) AS rata_rata_harga FROM produk;
MIN dan MAX — Nilai Terkecil dan Terbesar
SELECT
MIN(harga) AS harga_termurah,
MAX(harga) AS harga_termahal
FROM produk;
Dengan tanggal:
SELECT
MIN(dibuat_pada) AS pesanan_pertama,
MAX(dibuat_pada) AS pesanan_terakhir
FROM pesanan;
MIN dan MAX juga bekerja pada tipe string (berdasarkan urutan collation) dan tanggal.
Menggabungkan Beberapa Fungsi Agregasi
SELECT
COUNT(*) AS jumlah_produk,
SUM(stok) AS total_stok,
AVG(harga) AS rata_rata_harga,
MIN(harga) AS harga_termurah,
MAX(harga) AS harga_termahal
FROM produk;
Output contoh:
+---------------+------------+----------------+---------------+---------------+
| jumlah_produk | total_stok | rata_rata_harga| harga_termurah| harga_termahal|
+---------------+------------+----------------+---------------+---------------+
| 5 | 85 | 620000.0000| 150000.00 | 1500000.00 |
+---------------+------------+----------------+---------------+---------------+
GROUP BY — Mengelompokkan Data
Fungsi agregasi menjadi jauh lebih berguna saat dikombinasikan dengan GROUP BY. Alih-alih satu nilai untuk seluruh tabel, Anda mendapatkan satu nilai per kelompok.
Sintaks
SELECT kolom_grup, fungsi_agregasi(kolom)
FROM nama_tabel
GROUP BY kolom_grup;
Contoh: Jumlah Pesanan per Status
SELECT status, COUNT(*) AS jumlah
FROM pesanan
GROUP BY status;
Output contoh:
+---------+--------+
| status | jumlah |
+---------+--------+
| baru | 3 |
| selesai | 5 |
| batal | 1 |
+---------+--------+
Contoh: Total Penjualan per Pelanggan
SELECT pelanggan_id, SUM(total) AS total_belanja
FROM pesanan
GROUP BY pelanggan_id;
Contoh: Rata-rata Harga per Rentang Stok
SELECT
CASE
WHEN stok = 0 THEN 'habis'
WHEN stok < 10 THEN 'sedikit'
ELSE 'banyak'
END AS kategori_stok,
COUNT(*) AS jumlah_produk,
AVG(harga) AS rata_rata_harga
FROM produk
GROUP BY kategori_stok;
GROUP BY dengan Beberapa Kolom
SELECT
pelanggan_id,
status,
COUNT(*) AS jumlah,
SUM(total) AS total
FROM pesanan
GROUP BY pelanggan_id, status
ORDER BY pelanggan_id, status;
Setiap kombinasi (pelanggan_id, status) menjadi satu grup.
Aturan Penting: Kolom di SELECT Harus Ada di GROUP BY
Dalam mode ONLY_FULL_GROUP_BY (default di MySQL 5.7+), setiap kolom di SELECT yang bukan bagian dari fungsi agregasi harus muncul di GROUP BY.
-- ERROR di ONLY_FULL_GROUP_BY: nama tidak ada di GROUP BY
SELECT pelanggan_id, nama, SUM(total)
FROM pesanan
GROUP BY pelanggan_id;
-- Benar: nama diambil dari tabel pelanggan via JOIN, atau diagregasi
SELECT pelanggan_id, COUNT(*) AS jumlah_pesanan, SUM(total) AS total
FROM pesanan
GROUP BY pelanggan_id;
Untuk menonaktifkan sementara (tidak disarankan):
SET SESSION sql_mode = '';
Tips: Jangan matikan
ONLY_FULL_GROUP_BY. Error ini justru membantu Anda menulis query yang benar dan deterministik.
GROUP BY dengan ORDER BY
SELECT status, COUNT(*) AS jumlah
FROM pesanan
GROUP BY status
ORDER BY jumlah DESC;
Mengurutkan grup berdasarkan hasil agregasi.
HAVING — Memfilter Hasil Grup
WHERE memfilter baris sebelum pengelompokan. HAVING memfilter grup setelah pengelompokan.
Sintaks
SELECT kolom_grup, fungsi_agregasi(kolom)
FROM nama_tabel
GROUP BY kolom_grup
HAVING kondisi_agregasi;
Contoh: Grup dengan Jumlah Tertentu
SELECT pelanggan_id, COUNT(*) AS jumlah_pesanan
FROM pesanan
GROUP BY pelanggan_id
HAVING COUNT(*) > 1;
Hanya pelanggan yang memiliki lebih dari 1 pesanan.
Contoh: Grup dengan Total di Atas Ambang
SELECT pelanggan_id, SUM(total) AS total_belanja
FROM pesanan
GROUP BY pelanggan_id
HAVING SUM(total) > 1000000
ORDER BY total_belanja DESC;
WHERE vs HAVING
| Aspek | WHERE |
HAVING |
|---|---|---|
| Kapan dievaluasi | Sebelum GROUP BY |
Setelah GROUP BY |
| Bekerja pada | Baris individual | Grup |
| Bisa pakai fungsi agregasi? | Tidak | Ya |
| Contoh | WHERE status = 'selesai' |
HAVING COUNT(*) > 1 |
Menggabungkan WHERE dan HAVING
SELECT pelanggan_id, COUNT(*) AS jumlah_pesanan, SUM(total) AS total
FROM pesanan
WHERE dibuat_pada >= '2022-01-01'
GROUP BY pelanggan_id
HAVING SUM(total) > 500000
ORDER BY total DESC;
Alur eksekusi:
WHEREmemfilter pesanan yang dibuat sejak 2022-01-01.GROUP BYmengelompokkan per pelanggan.HAVINGmemfilter grup dengan total > 500.000.ORDER BYmengurutkan hasil.
WITH ROLLUP — Subtotal dan Grand Total
WITH ROLLUP menambahkan baris ringkasan (subtotal per grup dan grand total) ke hasil GROUP BY.
Sintaks
SELECT kolom_grup, fungsi_agregasi(kolom)
FROM nama_tabel
GROUP BY kolom_grup WITH ROLLUP;
Contoh
SELECT status, COUNT(*) AS jumlah
FROM pesanan
GROUP BY status WITH ROLLUP;
Output contoh:
+---------+--------+
| status | jumlah |
+---------+--------+
| baru | 3 |
| batal | 1 |
| selesai | 5 |
| NULL | 9 | ← grand total
+---------+--------+
Baris dengan status = NULL adalah grand total (9 pesanan).
Dengan beberapa kolom:
SELECT pelanggan_id, status, COUNT(*) AS jumlah
FROM pesanan
GROUP BY pelanggan_id, status WITH ROLLUP;
Akan menghasilkan subtotal per pelanggan_id dan grand total.
Memberi label pada baris rollup:
SELECT
IFNULL(pelanggan_id, 'TOTAL') AS pelanggan,
IFNULL(status, 'SEMUA') AS status,
COUNT(*) AS jumlah
FROM pesanan
GROUP BY pelanggan_id, status WITH ROLLUP;
Contoh Praktis: Analisis Penjualan
Mari gabungkan semua konsep untuk analisis penjualan:
1. Ringkasan Keseluruhan
SELECT
COUNT(*) AS total_pesanan,
COUNT(DISTINCT pelanggan_id) AS pelanggan_unik,
SUM(total) AS total_penjualan,
AVG(total) AS rata_rata_per_pesanan,
MIN(total) AS pesanan_terkecil,
MAX(total) AS pesanan_terbesar
FROM pesanan
WHERE status = 'selesai';
2. Penjualan per Status
SELECT
status,
COUNT(*) AS jumlah_pesanan,
SUM(total) AS total_penjualan,
ROUND(AVG(total), 2) AS rata_rata
FROM pesanan
GROUP BY status
ORDER BY total_penjualan DESC;
3. Top 5 Pelanggan dengan Belanja Tertinggi
SELECT
pelanggan_id,
COUNT(*) AS jumlah_pesanan,
SUM(total) AS total_belanja
FROM pesanan
WHERE status = 'selesai'
GROUP BY pelanggan_id
HAVING SUM(total) > 0
ORDER BY total_belanja DESC
LIMIT 5;
4. Produk Terlaris (dari detail_pesanan)
SELECT
produk_id,
SUM(jumlah) AS total_terjual,
SUM(jumlah * harga_satuan) AS total_pendapatan
FROM detail_pesanan
GROUP BY produk_id
ORDER BY total_terjual DESC
LIMIT 10;
5. Penjualan per Bulan
SELECT
DATE_FORMAT(dibuat_pada, '%Y-%m') AS bulan,
COUNT(*) AS jumlah_pesanan,
SUM(total) AS total_penjualan
FROM pesanan
WHERE status = 'selesai'
GROUP BY bulan
ORDER BY bulan;
6. Pelanggan Tanpa Pesanan
SELECT p.id, p.nama
FROM pelanggan p
LEFT JOIN pesanan ps ON ps.pelanggan_id = p.id
GROUP BY p.id, p.nama
HAVING COUNT(ps.id) = 0;
Query ini menggunakan LEFT JOIN (dibahas di artikel berikutnya) dan HAVING untuk menemukan pelanggan yang belum pernah memesan.
Fungsi Agregasi Lain yang Berguna
GROUP_CONCAT — Menggabungkan Nilai menjadi String
SELECT
pelanggan_id,
GROUP_CONCAT(status ORDER BY dibuat_pada SEPARATOR ', ') AS status_pesanan
FROM pesanan
GROUP BY pelanggan_id;
Output contoh:
+--------------+---------------------------+
| pelanggan_id | status_pesanan |
+--------------+---------------------------+
| 1 | baru, selesai, selesai |
| 2 | selesai, batal |
+--------------+---------------------------+
Berguna untuk menampilkan daftar nilai dalam satu baris.
STDDEV dan VARIANCE
SELECT
STDDEV(harga) AS standar_deviasi_harga,
VARIANCE(harga) AS variansi_harga
FROM produk;
Mengukur sebaran nilai — berguna untuk analisis statistik.
JSON_ARRAYAGG dan JSON_OBJECTAGG (MySQL 5.7+)
SELECT
pelanggan_id,
JSON_ARRAYAGG(status) AS status_json
FROM pesanan
GROUP BY pelanggan_id;
Menghasilkan array JSON dari nilai-nilai dalam grup.
Ringkasan
| Fungsi / Klausa | Fungsi |
|---|---|
COUNT(*) |
Menghitung semua baris |
COUNT(kolom) |
Menghitung baris dengan kolom tidak NULL |
COUNT(DISTINCT kolom) |
Menghitung nilai unik |
SUM(kolom) |
Menjumlahkan nilai |
AVG(kolom) |
Rata-rata |
MIN(kolom) / MAX(kolom) |
Nilai terkecil / terbesar |
GROUP BY |
Mengelompokkan baris |
HAVING |
Memfilter grup setelah agregasi |
WITH ROLLUP |
Menambah subtotal & grand total |
GROUP_CONCAT |
Menggabungkan nilai menjadi string |
Poin penting:
WHEREmemfilter baris sebelumGROUP BY;HAVINGmemfilter grup setelahGROUP BY.- Setiap kolom di
SELECTyang bukan fungsi agregasi harus muncul diGROUP BY(modeONLY_FULL_GROUP_BY). COUNT(*)menghitung semua baris;COUNT(kolom)mengabaikanNULL.SUMdanAVGmengabaikanNULL; gunakanIFNULLjika perlu nilai default 0.
Pada artikel berikutnya, kita akan membahas JOIN — cara menggabungkan data dari beberapa tabel yang saling berelasi.