Aggregate Functions

-- Basic aggregate functions
SELECT
    COUNT(*) AS total_orders,         -- Count all rows
    COUNT(DISTINCT customer_id) AS unique_customers, -- Count unique values
    SUM(total) AS revenue,            -- Sum of order totals
    AVG(total) AS average_order,      -- Mean value
    MIN(total) AS smallest_order,     -- Minimum
    MAX(total) AS largest_order       -- Maximum
FROM orders
WHERE created_at >= '2025-01-01';

-- GROUP BY: Aggregate per group
SELECT
    category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price,
    MIN(price) AS min_price,
    MAX(price) AS max_price
FROM products
GROUP BY category
ORDER BY product_count DESC;

-- GROUP BY with JOIN
SELECT
    c.name AS customer,
    COUNT(o.id) AS order_count,
    SUM(o.total) AS total_spent,
    AVG(o.total) AS avg_order_value
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
ORDER BY total_spent DESC;

-- HAVING: Filter groups (not rows)
SELECT
    category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING COUNT(*) >= 5          -- Groups with at least 5 products
   AND AVG(price) > 25.00;   -- Average price over $25

-- GROUP BY with date parts
SELECT
    YEAR(created_at) AS year,
    MONTH(created_at) AS month,
    COUNT(*) AS order_count,
    SUM(total) AS monthly_revenue
FROM orders
GROUP BY YEAR(created_at), MONTH(created_at)
ORDER BY year DESC, month DESC;

-- Multiple aggregations: Sales report
SELECT
    DATE(created_at) AS sale_date,
    COUNT(*) AS orders,
    SUM(total) AS revenue,
    AVG(total) AS avg_order,
    SUM(CASE WHEN status = 'refunded' THEN total ELSE 0 END) AS refunds
FROM orders
GROUP BY DATE(created_at)
ORDER BY sale_date DESC
LIMIT 30;