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 tabel produk, pelanggan, pesanan, dan detail_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 memiliki NULL.
  • COUNT(kolom) — menghitung baris yang kolomnya tidak NULL.

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: SUM mengabaikan nilai NULL. Jika semua baris NULL, hasilnya NULL (bukan 0). Gunakan IFNULL(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:

  1. WHERE memfilter pesanan yang dibuat sejak 2022-01-01.
  2. GROUP BY mengelompokkan per pelanggan.
  3. HAVING memfilter grup dengan total > 500.000.
  4. ORDER BY mengurutkan 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:

  • WHERE memfilter baris sebelum GROUP BY; HAVING memfilter grup setelah GROUP BY.
  • Setiap kolom di SELECT yang bukan fungsi agregasi harus muncul di GROUP BY (mode ONLY_FULL_GROUP_BY).
  • COUNT(*) menghitung semua baris; COUNT(kolom) mengabaikan NULL.
  • SUM dan AVG mengabaikan NULL; gunakan IFNULL jika perlu nilai default 0.

Pada artikel berikutnya, kita akan membahas JOIN — cara menggabungkan data dari beberapa tabel yang saling berelasi.

Top