Aggregate Functions (COUNT, SUM, AVG)

-- COUNT rows
SELECT COUNT(*) AS total_users 
FROM users;

-- COUNT non-null values
SELECT COUNT(email) AS users_with_email 
FROM users;

-- COUNT DISTINCT
SELECT COUNT(DISTINCT country) AS countries 
FROM users;

-- SUM
SELECT SUM(total) AS revenue 
FROM orders 
WHERE status = 'completed';

-- AVG
SELECT AVG(rating) AS avg_rating 
FROM reviews 
WHERE product_id = 123;

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

-- GROUP BY
SELECT 
  category,
  COUNT(*) AS product_count,
  AVG(price) AS avg_price
FROM products
GROUP BY category;

-- GROUP BY with ORDER BY
SELECT 
  user_id,
  COUNT(*) AS order_count,
  SUM(total) AS total_spent
FROM orders
GROUP BY user_id
ORDER BY total_spent DESC
LIMIT 10;

-- HAVING (filter groups)
SELECT 
  category,
  COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 10;

-- Multiple aggregates
SELECT 
  DATE(created_at) AS date,
  COUNT(*) AS orders,
  SUM(total) AS revenue,
  AVG(total) AS avg_order_value,
  MIN(total) AS min_order,
  MAX(total) AS max_order
FROM orders
GROUP BY DATE(created_at)
ORDER BY date DESC;

-- GROUP BY multiple columns
SELECT 
  country,
  city,
  COUNT(*) AS user_count
FROM users
GROUP BY country, city
ORDER BY user_count DESC;

-- Percentage calculation
SELECT 
  status,
  COUNT(*) AS count,
  COUNT(*) * 100.0 / (SELECT COUNT(*) FROM orders) AS percentage
FROM orders
GROUP BY status;