SELECT 
    column1,
    column2,
    aggregate_function(column3)
FROM table
WHERE condition           -- Filters before grouping
GROUP BY column1, column2
HAVING aggregate_condition -- Filters after grouping
ORDER BY

SELECT 
    category,
    COUNT(*) as product_count,
    AVG(price) as avg_price,
    MIN(price) as min_price,
    MAX(price) as max_price,
    SUM(quantity_in_stock) as total_inventory
FROM products
GROUP BY category
ORDER BY product_count DESC

SELECT 
    customer_id,
    COUNT(*) as order_count,
    SUM(total) as total_spent,
    AVG(total) as avg_order_value,
    MAX(order_date) as last_order_date
FROM orders
WHERE order_date >= '2024-01-01'  -- Filter BEFORE grouping
GROUP BY customer_id
HAVING COUNT(*) >= 3              -- Filter AFTER grouping
   AND SUM(total) > 500
ORDER BY total_spent DESC

SELECT 
    department,
    job_title,
    COUNT(*) as employee_count,
    AVG(salary) as avg_salary,
    MIN(salary) as min_salary,
    MAX(salary) as max_salary,
    SUM(salary) as total_payroll
FROM employees
GROUP BY department, job_title
HAVING COUNT(*) > 1  -- Only show roles with multiple employees
ORDER BY department, avg_salary DESC

SELECT 
    product_category,
    EXTRACT(YEAR FROM sale_date) as sale_year,
    COUNT(*) as transaction_count,
    SUM(quantity) as units_sold,
    SUM(revenue) as total_revenue,
    AVG(revenue) as avg_transaction_value
FROM sales
WHERE 
    sale_date >= '2023-01-01'           -- WHERE: Filter rows before grouping
    AND product_category != 'Discontinued'
GROUP BY 
    product_category, 
    EXTRACT(YEAR FROM sale_date)
HAVING 
    SUM(revenue) > 10000                -- HAVING: Filter groups after aggregation
    AND COUNT(*) >= 50
ORDER BY 
    sale_year DESC, 
    total_revenue DESC

-- GROUPING SETS: Specify exact grouping combinations
SELECT 
    region,
    product_category,
    SUM(revenue) as total_revenue
FROM sales
GROUP BY GROUPING SETS (
    (region, product_category),  -- By region and category
    (region),                     -- By region only
    (product_category),          -- By category only
    ()                           -- Grand total
)
ORDER BY region NULLS LAST, product_category NULLS LAST;

-- CUBE: All possible combinations
SELECT 
    region,
    product_category,
    SUM(revenue) as total_revenue
FROM sales
GROUP BY CUBE(region, product_category);

-- ROLLUP: Hierarchical aggregations
SELECT 
    year,
    quarter,
    month,
    SUM(revenue) as total_revenue
FROM sales_data
GROUP BY ROLLUP(year, quarter, month)
ORDER BY year, quarter, month

SELECT 
    CASE 
        WHEN age < 25 THEN '18-24'
        WHEN age < 35 THEN '25-34'
        WHEN age < 50 THEN '35-49'
        ELSE '50+'
    END as age_group,
    COUNT(*) as customer_count,
    AVG(lifetime_value) as avg_lifetime_value,
    SUM(total_purchases) as total_purchases
FROM customers
GROUP BY 
    CASE 
        WHEN age < 25 THEN '18-24'
        WHEN age < 35 THEN '25-34'
        WHEN age < 50 THEN '35-49'
        ELSE '50+'
    END
HAVING COUNT(*) >= 10
ORDER BY