Aggregations & GROUP BY

-- Count all rows
SELECT COUNT(*) AS total_orders FROM orders;

-- Count non-NULL values
SELECT COUNT(phone) AS customers_with_phone FROM customers;

-- Count distinct values
SELECT COUNT(DISTINCT category_id) AS unique_categories FROM products;

-- Count with condition
SELECT COUNT(*) FILTER (WHERE status = 'active') AS active_count -- PostgreSQL
FROM users;

SELECT COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count -- All DBs
FROM users;

-- Total of a column
SELECT SUM(total) AS total_revenue FROM orders;

-- Sum with condition
SELECT 
    SUM(total) AS total_revenue,
    SUM(CASE WHEN status = 'completed' THEN total ELSE 0 END) AS completed_revenue
FROM orders;

-- Sum of expression
SELECT SUM(quantity * price) AS total_value FROM order_items;

-- Average value
SELECT AVG(price) AS avg_price FROM products;

-- Average with precision
SELECT ROUND(AVG(price), 2) AS avg_price FROM products;

-- Weighted average
SELECT 
    SUM(price * quantity) / SUM(quantity) AS weighted_avg_price
FROM order_items;

-- Minimum and maximum
SELECT 
    MIN(price) AS cheapest,
    MAX(price) AS most_expensive
FROM products;

-- For dates
SELECT 
    MIN(created_at) AS first_order,
    MAX(created_at) AS last_order
FROM orders;

-- For strings (alphabetical)
SELECT 
    MIN(name) AS first_alphabetically,
    MAX(name) AS last_alphabetically
FROM products;

-- PostgreSQL: STRING_AGG
SELECT 
    customer_id,
    STRING_AGG(product_name, ', ') AS products_ordered
FROM order_items
GROUP BY customer_id;

-- MySQL: GROUP_CONCAT
SELECT 
    customer_id,
    GROUP_CONCAT(product_name SEPARATOR ', ') AS products_ordered
FROM order_items
GROUP BY customer_id;

-- SQL Server: STRING_AGG (2017+)
SELECT 
    customer_id,
    STRING_AGG(product_name, ', ') AS products_ordered
FROM order_items
GROUP BY customer_id;

-- With ordering
SELECT 
    customer_id,
    STRING_AGG(product_name, ', ' ORDER BY product_name) AS products_ordered -- PostgreSQL
FROM order_items
GROUP BY customer_id;

-- Count orders per customer
SELECT 
    customer_id,
    COUNT(*) AS order_count,
    SUM(total) AS total_spent
FROM orders
GROUP BY customer_id;

-- Sales by category and year
SELECT 
    category_id,
    EXTRACT(YEAR FROM created_at) AS year,
    COUNT(*) AS order_count,
    SUM(total) AS revenue
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
GROUP BY category_id, EXTRACT(YEAR FROM created_at)
ORDER BY category_id, year;

-- Group by expression
SELECT 
    CASE 
        WHEN price < 100 THEN 'Budget'
        WHEN price < 500 THEN 'Mid-range'
        ELSE 'Premium'
    END AS price_tier,
    COUNT(*) AS product_count,
    ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY 
    CASE 
        WHEN price < 100 THEN 'Budget'
        WHEN price < 500 THEN 'Mid-range'
        ELSE 'Premium'
    END;

-- Monthly revenue
SELECT 
    DATE_TRUNC('month', created_at) AS month,  -- PostgreSQL
    -- DATE_FORMAT(created_at, '%Y-%m-01') AS month,  -- MySQL
    COUNT(*) AS order_count,
    SUM(total) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

-- Daily, weekly, monthly options
SELECT 
    DATE_TRUNC('day', created_at) AS day,
    DATE_TRUNC('week', created_at) AS week,
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*)
FROM orders
GROUP BY 
    DATE_TRUNC('day', created_at),
    DATE_TRUNC('week', created_at),
    DATE_TRUNC('month', created_at);

-- ❌ ERROR: Can't use aggregate in WHERE
SELECT customer_id, COUNT(*)
FROM orders
WHERE COUNT(*) > 5  -- ERROR!
GROUP BY customer_id;

-- ✅ CORRECT: Use HAVING for aggregate conditions
SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

-- WHERE filters BEFORE grouping
SELECT 
    customer_id,
    COUNT(*) AS order_count,
    SUM(total) AS total_spent
FROM orders
WHERE status = 'completed'  -- Only count completed orders
GROUP BY customer_id;

-- HAVING filters AFTER grouping
SELECT 
    customer_id,
    COUNT(*) AS order_count,
    SUM(total) AS total_spent
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 5  -- Only show customers with 5+ orders
   AND SUM(total) > 1000;  -- AND spent over $1000

-- Use both for complex filtering
SELECT 
    category_id,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price
FROM products
WHERE stock > 0          -- Only in-stock products (row filter)
GROUP BY category_id
HAVING COUNT(*) >= 5     -- Categories with 5+ products (group filter)
   AND AVG(price) > 100  -- And average price over $100
ORDER BY avg_price DESC;

-- ROLLUP: Hierarchical subtotals
SELECT 
    COALESCE(region, 'ALL REGIONS') AS region,
    COALESCE(category, 'ALL CATEGORIES') AS category,
    SUM(sales) AS total_sales
FROM sales
GROUP BY ROLLUP(region, category);

-- CUBE: All possible combinations
SELECT 
    region,
    category,
    SUM(sales) AS total_sales
FROM sales
GROUP BY CUBE(region, category);

-- GROUPING SETS: Specific combinations
SELECT 
    region,
    category,
    SUM(sales) AS total_sales
FROM sales
GROUP BY GROUPING SETS (
    (region, category),  -- Combination
    (region),            -- By region only
    (category),          -- By category only
    ()                   -- Grand total
);

-- Equivalent to CUBE(region, category)