GROUP BY dan HAVING: Filter Data Agregat

Halo SQL Explorers! Setelah belajar fungsi agregat, sekarang kita bakal belajar lebih dalam tentang GROUP BY dan HAVING. Ini adalah tools penting untuk analisis data yang lebih kompleks!

GROUP BY: Mengelompokkan Data

-- Grouping sederhana: jumlah produk per kategori
SELECT 
    c.nama as kategori,
    COUNT(*) as jumlah_produk
FROM categories c
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 |
+------------+---------------+

Multiple GROUP BY

-- Analisis penjualan per kategori dan status order
SELECT 
    c.nama as kategori,
    o.status,
    COUNT(DISTINCT o.id) as jumlah_order,
    SUM(oi.quantity) as total_quantity,
    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
JOIN orders o ON oi.order_id = o.id
GROUP BY c.id, c.nama, o.status;

-- Hasilnya:
+------------+---------+--------------+----------------+------------------+
| kategori   | status  | jumlah_order | total_quantity | total_penjualan  |
+------------+---------+--------------+----------------+------------------+
| Elektronik | success |            2 |              2 |         20000000 |
| Fashion    | success |            1 |              2 |           750000 |
| Makanan    | success |            1 |              5 |           250000 |
+------------+---------+--------------+----------------+------------------+

HAVING: Filter Hasil Agregat

-- Cari kategori dengan total penjualan > 1jt
SELECT 
    c.nama as kategori,
    COUNT(DISTINCT o.id) as jumlah_order,
    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
JOIN orders o ON oi.order_id = o.id
GROUP BY c.id, c.nama
HAVING total_penjualan > 1000000;

-- Hasilnya:
+------------+--------------+------------------+
| kategori   | jumlah_order | total_penjualan  |
+------------+--------------+------------------+
| Elektronik |            2 |         20000000 |
+------------+--------------+------------------+

-- Cari customer yang order lebih dari 1x
SELECT 
    c.nama,
    COUNT(o.id) as jumlah_order,
    SUM(oi.price * oi.quantity) as total_belanja
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY c.id, c.nama
HAVING jumlah_order > 1;

-- Hasilnya:
+------+--------------+---------------+
| nama | jumlah_order | total_belanja |
+------+--------------+---------------+
| Budi |            2 |      20000000 |
+------+--------------+---------------+

WHERE vs HAVING

-- Dengan WHERE (filter sebelum grouping)
SELECT 
    c.nama as kategori,
    COUNT(*) as jumlah_produk
FROM categories c
JOIN products p ON c.id = p.category_id
WHERE p.harga > 1000000  -- Filter dulu
GROUP BY c.id, c.nama;

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

-- Dengan HAVING (filter setelah grouping)
SELECT 
    c.nama as kategori,
    COUNT(*) as jumlah_produk,
    AVG(p.harga) as rata_rata_harga
FROM categories c
JOIN products p ON c.id = p.category_id
GROUP BY c.id, c.nama
HAVING AVG(p.harga) > 1000000;  -- Filter hasil agregat

-- Hasilnya:
+------------+---------------+----------------+
| kategori   | jumlah_produk | rata_rata_harga|
+------------+---------------+----------------+
| Elektronik |             2 |       10000000 |
+------------+---------------+----------------+

Tips Penggunaan GROUP BY dan HAVING

Best Practices GROUP BY

-- Selalu include semua kolom non-agregat
SELECT 
    c.nama as kategori,
    p.category_id,  -- Harus ada di GROUP BY
    COUNT(*) as jumlah
FROM categories c
JOIN products p ON c.id = p.category_id
GROUP BY c.nama, p.category_id;

Optimasi Query

-- Gunakan WHERE sebelum GROUP BY
-- Filter data yang tidak perlu seawal mungkin
SELECT 
    c.nama as kategori,
    COUNT(*) as jumlah_produk
FROM categories c
JOIN products p ON c.id = p.category_id
WHERE p.harga > 0  -- Filter dulu
GROUP BY c.nama;

Complex Grouping

-- Grouping dengan multiple kondisi
SELECT 
    c.nama as kategori,
    CASE 
        WHEN p.harga > 1000000 THEN 'Mahal'
        WHEN p.harga > 100000 THEN 'Sedang'
        ELSE 'Murah'
    END as kategori_harga,
    COUNT(*) as jumlah_produk
FROM categories c
JOIN products p ON c.id = p.category_id
GROUP BY 
    c.nama,
    CASE 
        WHEN p.harga > 1000000 THEN 'Mahal'
        WHEN p.harga > 100000 THEN 'Sedang'
        ELSE 'Murah'
    END;

-- Hasilnya:
+------------+----------------+---------------+
| kategori   | kategori_harga | jumlah_produk |
+------------+----------------+---------------+
| Elektronik | Mahal          |             2 |
| Fashion    | Sedang         |             2 |
| Makanan    | Murah          |             2 |
+------------+----------------+---------------+

Latihan Praktik

Analisis penjualan bulanan:

SELECT 
    DATE_FORMAT(o.tanggal, '%Y-%m') as bulan,
    COUNT(DISTINCT o.id) as jumlah_order,
    COUNT(DISTINCT o.customer_id) as jumlah_customer,
    SUM(oi.quantity * oi.price) as total_penjualan
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
GROUP BY DATE_FORMAT(o.tanggal, '%Y-%m')
HAVING total_penjualan > 1000000;

-- Hasilnya akan tergantung tanggal order di database

Cari kategori dengan rata-rata order value tertinggi:

SELECT 
    c.nama as kategori,
    ROUND(AVG(oi.price * oi.quantity), 2) as avg_order_value,
    COUNT(DISTINCT o.id) as jumlah_order
FROM categories c
JOIN products p ON c.id = p.category_id
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
GROUP BY c.id, c.nama
HAVING jumlah_order > 0
ORDER BY avg_order_value DESC
LIMIT 1;

-- Hasilnya:
+------------+----------------+--------------+
| kategori   | avg_order_value| jumlah_order |
+------------+----------------+--------------+
| Elektronik |       10000000 |            2 |
+------------+----------------+--------------+

Materi Selanjutnya

Di materi selanjutnya, kita bakal belajar:

Views untuk menyimpan query kompleks
Indexes untuk optimasi performa
Dan masih banyak lagi!