GROUP BY + Aggregate Kombinasyonları

Giriş — Gruplama Gücünü Keşfetme

GROUP BY'ın temellerini öğrendik. Şimdi aggregate fonksiyonlarla birlikte daha karmaşık ve gerçekçi sorgular yazacağız. Bu ders, önceki derslerde öğrendiğin her şeyi birleştiren bir "uygulama dersi" niteliğinde.

Çoklu Aggregate ile Kapsamlı Raporlar

-- Müşteri bazlı kapsamlı sipariş raporu
SELECT 
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    c.city,
    COUNT(o.order_id) AS total_orders,
    COALESCE(SUM(o.total_amount), 0) AS total_spent,
    COALESCE(ROUND(AVG(o.total_amount), 2), 0) AS avg_order,
    COALESCE(MAX(o.total_amount), 0) AS largest_order,
    MAX(o.order_date) AS last_order_date,
    DATEDIFF(NOW(), MAX(o.order_date)) AS days_since_last_order
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.status != 'cancelled'
GROUP BY c.customer_id, c.first_name, c.last_name, c.city
ORDER BY total_spent DESC;

💡 İpucu: LEFT JOIN kullandığımız için sipariş vermemiş müşteriler de sonuçta görünür (NULL değerlerle). COALESCE ile bunları 0'a çeviriyoruz.

Koşullu Aggregate (Pivot Benzeri)

-- Şehir bazında sipariş durumu dağılımı
SELECT 
    c.city,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN o.status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
    SUM(CASE WHEN o.status = 'pending' THEN 1 ELSE 0 END) AS pending,
    SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
    ROUND(SUM(CASE WHEN o.status = 'delivered' THEN o.total_amount ELSE 0 END), 2) AS delivered_revenue,
    ROUND(
        SUM(CASE WHEN o.status = 'delivered' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1
    ) AS delivery_rate
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.city
ORDER BY total_orders DESC;

Bu sorgu her şehir için ayrı sütunlarda durum dağılımı gösterir — CASE WHEN + SUM ile "pivot" benzeri bir çıktı üretir.

GROUP BY ile Tarih Gruplama

-- Yıllık rapor
SELECT YEAR(order_date) AS year, COUNT(*), SUM(total_amount) AS revenue
FROM orders GROUP BY YEAR(order_date);

-- Aylık rapor
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
       COUNT(*) AS orders, 
       ROUND(SUM(total_amount), 2) AS revenue
FROM orders 
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;

-- Haftalık rapor
SELECT YEARWEEK(order_date) AS week,
       COUNT(*) AS orders
FROM orders
GROUP BY YEARWEEK(order_date)
ORDER BY week;

-- Haftanın günlerine göre
SELECT DAYNAME(order_date) AS day_name,
       COUNT(*) AS orders,
       ROUND(AVG(total_amount), 2) AS avg_order
FROM orders
GROUP BY DAYNAME(order_date), DAYOFWEEK(order_date)
ORDER BY DAYOFWEEK(order_date);

GROUP BY ile JOIN

-- Kategori bazlı satış raporu
SELECT 
    c.category_name,
    COUNT(DISTINCT p.product_id) AS product_count,
    COUNT(oi.item_id) AS times_ordered,
    COALESCE(SUM(oi.quantity), 0) AS total_units_sold,
    ROUND(COALESCE(SUM(oi.quantity * oi.unit_price), 0), 2) AS total_revenue
FROM categories c
LEFT JOIN products p ON c.category_id = p.category_id
LEFT JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY c.category_id, c.category_name
ORDER BY total_revenue DESC;

Nested Aggregate (İç İçe)

Doğrudan iç içe aggregate yazamazsın ama subquery ile çözebilirsin:

-- Müşteri başına ortalama sipariş sayısının ortalaması
-- (Yani ortalama bir müşteri kaç sipariş veriyor?)
SELECT ROUND(AVG(order_count), 2) AS avg_orders_per_customer
FROM (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
) AS customer_orders;

-- En çok sipariş veren müşterinin sipariş sayısı
SELECT MAX(order_count) AS max_orders
FROM (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
) AS customer_orders;

Gerçek Dünya Örneği — Ürün Performans Raporu

-- Her ürünün satış performansı
SELECT 
    p.product_id,
    p.product_name,
    p.price AS current_price,
    p.stock_quantity,
    COALESCE(SUM(oi.quantity), 0) AS units_sold,
    COALESCE(ROUND(SUM(oi.quantity * oi.unit_price), 2), 0) AS total_revenue,
    COALESCE(ROUND(AVG(oi.unit_price), 2), 0) AS avg_selling_price,
    COALESCE(COUNT(DISTINCT o.customer_id), 0) AS unique_buyers,
    -- Stok devir hızı tahmini
    CASE 
        WHEN COALESCE(SUM(oi.quantity), 0) = 0 THEN 'Hiç satılmadı'
        WHEN p.stock_quantity / (SUM(oi.quantity) / 
             GREATEST(DATEDIFF(NOW(), MIN(o.order_date)), 1)) < 30 
        THEN 'Kritik stok'
        ELSE 'Normal'
    END AS stock_status
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.order_id AND o.status != 'cancelled'
WHERE p.is_active = TRUE
GROUP BY p.product_id, p.product_name, p.price, p.stock_quantity
ORDER BY total_revenue DESC;

Sıkça Yapılan Hatalar

1. GROUP BY'da olmayan sütunu SELECT'e yazmak: Her non-aggregate sütun GROUP BY'da olmalı.

2. LEFT JOIN sonrası COUNT(*) kullanmak: Eşleşme olmayan satırlar da 1 sayılır. COUNT(sağ_tablo.sütun) kullan.

3. Aggregate'lerin NULL sonuç vermesi: LEFT JOIN sonrası SUM/AVG NULL dönebilir. COALESCE kullan.

Özet

• GROUP BY + çoklu aggregate = kapsamlı raporlar

• CASE WHEN + SUM/COUNT = koşullu gruplama (pivot-like)

• Tarih fonksiyonlarıyla GROUP BY = zaman bazlı raporlar

• LEFT JOIN + GROUP BY = eksik verileri de gösteren raporlar

• İç içe aggregate → subquery ile çözülür

• LEFT JOIN sonrası COUNT(*) yerine COUNT(sütun) kullan