GROUP BY ve Aggregate Fonksiyonlar

Aggregate Fonksiyonlar

Fonksiyon	Açıklama	Örnek Sonuç
COUNT(*)	Tüm satır sayısı	150
COUNT(column)	NULL olmayan satır sayısı	142
COUNT(DISTINCT col)	Benzersiz değer sayısı	45
SUM(col)	Toplam	12500.00
AVG(col)	Ortalama	89.50
MIN(col)	Minimum değer	10.00
MAX(col)	Maksimum değer	5000.00
STRING_AGG(col, sep)	Metinleri birleştir	'a, b, c'
ARRAY_AGG(col)	Dizi oluştur	{a, b, c}

Temel Aggregate Kullanımı

-- Toplam kullanıcı sayısı
SELECT COUNT(*) AS total_users FROM users;

-- Aktif kullanıcı sayısı
SELECT COUNT(*) AS active_users FROM users WHERE status = 'active';

-- Benzersiz ülke sayısı
SELECT COUNT(DISTINCT country) AS country_count FROM users;

-- Sipariş istatistikleri
SELECT 
    COUNT(*) AS total_orders,
    SUM(total_amount) AS total_revenue,
    ROUND(AVG(total_amount), 2) AS avg_order_value,
    MIN(total_amount) AS min_order,
    MAX(total_amount) AS max_order
FROM orders
WHERE status = 'completed';

total_orders | total_revenue | avg_order_value | min_order | max_order --------------+---------------+-----------------+-----------+----------- 1250 | 187500.00 | 150.00 | 15.00 | 2500.00

GROUP BY - Gruplama

-- Kategoriye göre ürün sayısı ve fiyat bilgileri
SELECT 
    category,
    COUNT(*) AS product_count,
    ROUND(AVG(price), 2) AS avg_price,
    MIN(price) AS min_price,
    MAX(price) AS max_price,
    SUM(stock) AS total_stock
FROM products
GROUP BY category
ORDER BY product_count DESC;

category | product_count | avg_price | min_price | max_price | total_stock ---------------+---------------+-----------+-----------+-----------+------------- Electronics | 45 | 850.00 | 50.00 | 5000.00 | 234 Clothing | 32 | 120.00 | 25.00 | 500.00 | 567 Furniture | 18 | 1200.00 | 200.00 | 8000.00 | 89

Çoklu Kolon GROUP BY

-- Yıl ve ay bazında satış raporu
SELECT 
    EXTRACT(YEAR FROM created_at) AS year,
    EXTRACT(MONTH FROM created_at) AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_sales,
    ROUND(AVG(total_amount), 2) AS avg_order
FROM orders
WHERE status = 'completed'
GROUP BY 
    EXTRACT(YEAR FROM created_at),
    EXTRACT(MONTH FROM created_at)
ORDER BY year DESC, month DESC;

HAVING - Grup Filtreleme

WHERE aggregate fonksiyonlarla çalışmaz, HAVING kullanılır.

-- 1000 TL üzeri harcama yapan müşteriler
SELECT 
    user_id,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_spent
FROM orders
WHERE status = 'completed'
GROUP BY user_id
HAVING SUM(total_amount) > 1000
ORDER BY total_spent DESC;

-- 10'dan fazla ürünü olan kategoriler
SELECT 
    category,
    COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 10;

WHERE vs HAVING: WHERE gruplama öncesi, HAVING gruplama sonrası filtreler.

STRING_AGG - Metin Birleştirme

-- Her kullanıcının sipariş numaraları
SELECT 
    u.username,
    STRING_AGG(o.order_number, ', ' ORDER BY o.created_at) AS orders
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

username | orders ----------+---------------------------------- ahmet | ORD-001, ORD-005, ORD-012 mehmet | ORD-003, ORD-008

ROLLUP - Alt Toplamlar

SELECT 
    COALESCE(category, 'GENEL TOPLAM') AS category,
    COALESCE(brand, 'Kategori Toplamı') AS brand,
    COUNT(*) AS product_count,
    SUM(price * stock) AS inventory_value
FROM products
GROUP BY ROLLUP(category, brand)
ORDER BY category, brand;