MySQL GROUP BY

-- Count products per category
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category;

-- Total stock and average price per category
SELECT
    category,
    COUNT(*)          AS product_count,
    SUM(stock)        AS total_stock,
    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;

-- Total revenue per order
SELECT
    order_id,
    SUM(quantity * unit_price) AS order_total,
    COUNT(*)                   AS item_count
FROM order_items
GROUP BY order_id;

-- GROUP_CONCAT: combine values from a group into a comma-separated string
SELECT
    category,
    GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS product_names
FROM products
GROUP BY category;

-- Categories with more than 2 products
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING product_count > 2;

-- Customers who have placed more than 1 order
SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS total_spent
FROM orders
GROUP BY customer_id
HAVING order_count > 1
ORDER BY total_spent DESC;

-- WHERE vs HAVING:
-- WHERE filters rows BEFORE grouping (faster, uses indexes)
-- HAVING filters groups AFTER aggregation

-- Only count active products, then show categories with avg price > 50
SELECT category, COUNT(*) AS product_count, AVG(price) AS avg_price
FROM products
WHERE stock > 0          -- WHERE: filter rows before grouping
GROUP BY category
HAVING avg_price > 30    -- HAVING: filter groups after aggregation
ORDER BY avg_price DESC;

-- Group by multiple columns: orders per customer per status
SELECT customer_id, status, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id, status
ORDER BY customer_id, status;

-- WITH ROLLUP: adds subtotal and grand total rows
-- NULL in the grouping column indicates a subtotal/grand total row
SELECT
    COALESCE(category, 'TOTAL') AS category,
    COUNT(*)                    AS product_count,
    SUM(stock)                  AS total_stock
FROM products
GROUP BY category WITH ROLLUP;

-- DISTINCT vs GROUP BY (equivalent for simple deduplication)
-- These two queries return the same result:
SELECT DISTINCT category FROM products;
SELECT category FROM products GROUP BY category;

-- Monthly sales summary
SELECT
    YEAR(ordered_at)  AS year,
    MONTH(ordered_at) AS month,
    COUNT(*)          AS order_count,
    SUM(total)        AS monthly_revenue,
    AVG(total)        AS avg_order_value
FROM orders
WHERE status = 'delivered'
GROUP BY YEAR(ordered_at), MONTH(ordered_at)
ORDER BY year DESC, month DESC;

-- Top 5 best-selling products by quantity
SELECT
    p.name,
    SUM(oi.quantity)              AS total_sold,
    SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM order_items oi
INNER JOIN products p ON oi.product_id = p.product_id
GROUP BY oi.product_id, p.name
ORDER BY total_sold DESC
LIMIT 5;