Aggregate Functions: Fungsi Agregat SQL 📊

Halo SQL Explorers! 👋 Setelah belajar subquery, sekarang kita bakal belajar tentang fungsi agregat. Ini adalah tools powerful buat analisis data!

Apa itu Fungsi Agregat? 📊

Fungsi agregat adalah fungsi yang melakukan perhitungan pada sekelompok baris dan mengembalikan satu nilai. Fungsi yang sering dipakai:

  • COUNT(): Menghitung jumlah baris
  • SUM(): Menjumlahkan nilai
  • AVG(): Menghitung rata-rata
  • MAX(): Mencari nilai tertinggi
  • MIN(): Mencari nilai terendah

COUNT(): Menghitung Data 🔢

-- Hitung jumlah produk per kategori
SELECT 
    c.nama as kategori,
    COUNT(p.id) as jumlah_produk
FROM categories c
LEFT JOIN products p ON c.id = p.category_id
GROUP BY c.id, c.nama;

-- Hasilnya:
+------------+---------------+
| kategori   | jumlah_produk |
+------------+---------------+
| Elektronik |             2 |
| Fashion    |             2 |
| Makanan    |             2 |
+------------+---------------+

-- Hitung jumlah order per customer
SELECT 
    c.nama,
    COUNT(o.id) as jumlah_order
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.nama;

-- Hasilnya:
+------+--------------+
| nama | jumlah_order |
+------+--------------+
| Budi |            2 |
| Ani  |            1 |
| Caca |            1 |
| Dodi |            0 |
+------+--------------+

SUM(): Menjumlahkan Nilai 💰

-- Hitung total penjualan per kategori
SELECT 
    c.nama as kategori,
    SUM(oi.price * oi.quantity) as total_penjualan
FROM categories c
JOIN products p ON c.id = p.category_id
JOIN order_items oi ON p.id = oi.product_id
GROUP BY c.id, c.nama;

-- Hasilnya:
+------------+------------------+
| kategori   | total_penjualan  |
+------------+------------------+
| Elektronik |         20000000 |
| Fashion    |           750000 |
| Makanan    |           250000 |
+------------+------------------+

-- Hitung total belanja per customer
SELECT 
    c.nama,
    COALESCE(SUM(oi.price * oi.quantity), 0) as total_belanja
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN order_items oi ON o.id = oi.order_id
GROUP BY c.id, c.nama;

-- Hasilnya:
+------+---------------+
| nama | total_belanja |
+------+---------------+
| Budi |      20000000 |
| Ani  |        750000 |
| Caca |        250000 |
| Dodi |             0 |
+------+---------------+

AVG(): Menghitung Rata-rata 📈

-- Hitung rata-rata harga produk per kategori
SELECT 
    c.nama as kategori,
    ROUND(AVG(p.harga), 2) as rata_rata_harga
FROM categories c
JOIN products p ON c.id = p.category_id
GROUP BY c.id, c.nama;

-- Hasilnya:
+------------+----------------+
| kategori   | rata_rata_harga|
+------------+----------------+
| Elektronik |      10000000 |
| Fashion    |        275000 |
| Makanan    |         32500 |
+------------+----------------+

-- Hitung rata-rata quantity per order
SELECT 
    o.id as order_id,
    c.nama as customer,
    ROUND(AVG(oi.quantity), 2) as rata_rata_quantity
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id, c.nama;

-- Hasilnya:
+----------+----------+------------------+
| order_id | customer | rata_rata_quantity|
+----------+----------+------------------+
|        1 | Budi    |              1.00 |
|        2 | Budi    |              1.00 |
|        3 | Ani     |              1.50 |
|        4 | Caca    |              5.00 |
+----------+----------+------------------+

MAX() dan MIN(): Nilai Ekstrim 📊

-- Cari harga tertinggi dan terendah per kategori
SELECT 
    c.nama as kategori,
    MAX(p.harga) as harga_tertinggi,
    MIN(p.harga) as harga_terendah
FROM categories c
JOIN products p ON c.id = p.category_id
GROUP BY c.id, c.nama;

-- Hasilnya:
+------------+----------------+---------------+
| kategori   | harga_tertinggi| harga_terendah|
+------------+----------------+---------------+
| Elektronik |       15000000 |       5000000 |
| Fashion    |         350000 |        200000 |
| Makanan    |          50000 |         15000 |
+------------+----------------+---------------+

Kombinasi Fungsi Agregat 🔄

-- Statistik lengkap per kategori
SELECT 
    c.nama as kategori,
    COUNT(p.id) as jumlah_produk,
    MIN(p.harga) as harga_terendah,
    MAX(p.harga) as harga_tertinggi,
    ROUND(AVG(p.harga), 2) as rata_rata_harga,
    SUM(p.stok) as total_stok
FROM categories c
JOIN products p ON c.id = p.category_id
GROUP BY c.id, c.nama;

-- Hasilnya:
+------------+---------------+---------------+----------------+----------------+------------+
| kategori   | jumlah_produk | harga_terendah| harga_tertinggi| rata_rata_harga| total_stok|
+------------+---------------+---------------+----------------+----------------+------------+
| Elektronik |             2 |       5000000 |       15000000 |       10000000 |        15 |
| Fashion    |             2 |        200000 |         350000 |         275000 |        35 |
| Makanan    |             2 |         15000 |          50000 |          32500 |       250 |
+------------+---------------+---------------+----------------+----------------+------------+

Tips Menggunakan Fungsi Agregat 💡

  1. Perhatikan NULL Values

    • Gunakan COALESCE untuk handle NULL
    • COUNT(*) vs COUNT(kolom)
    • Pertimbangkan LEFT JOIN vs INNER JOIN

  2. Formatting Output

    • ROUND() untuk angka desimal
    • FORMAT() untuk format uang
    • CAST() untuk konversi tipe data

  3. Performance Tips

    • Index kolom yang sering di-GROUP BY
    • Hindari GROUP BY yang terlalu banyak kolom
    • Gunakan WHERE sebelum GROUP BY

Materi Selanjutnya 📚

Di materi selanjutnya, kita bakal belajar:

  • GROUP BY lebih detail
  • HAVING clause
  • Filtering hasil agregat
  • Dan masih banyak lagi! 🚀
Fatkul Nur Koirudin