#Aggregate Functions

-- Count all rows
SELECT COUNT(*) FROM users;

-- Count non-NULL values
SELECT COUNT(email) FROM users;

-- Count different values
SELECT COUNT(DISTINCT city) FROM users;

-- Calculate total amount of all orders
SELECT SUM(total_amount) FROM orders;

-- Calculate total amount for specific user
SELECT SUM(total_amount) FROM orders WHERE user_id = 1;

-- Calculate average age of users
SELECT AVG(age) FROM users;

-- Calculate average amount of orders
SELECT AVG(total_amount) FROM orders;

-- Find minimum age
SELECT MIN(age) FROM users;

-- Find maximum age
SELECT MAX(age) FROM users;

-- Find earliest order
SELECT MIN(order_date) FROM orders;

-- Group by city, count users per city
SELECT
    city,
    COUNT(*) AS user_count
FROM users
GROUP BY city;

-- Group by city and gender
SELECT
    city,
    gender,
    COUNT(*) AS user_count
FROM users
GROUP BY city, gender;

-- Find cities with more than 100 users
SELECT
    city,
    COUNT(*) AS user_count
FROM users
GROUP BY city
HAVING COUNT(*) > 100;

SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_sales,
    AVG(total_amount) AS avg_order_value
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;

SELECT
    u.id AS user_id,
    u.username,
    COUNT(o.id) AS order_count,
    SUM(o.total_amount) AS total_spent,
    AVG(o.total_amount) AS avg_order_value,
    MAX(o.order_date) AS last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01'
GROUP BY u.id
ORDER BY total_spent DESC;

-- Not recommended
SELECT city, COUNT(*)
FROM users
GROUP BY city
HAVING city = 'Beijing';

-- Recommended
SELECT city, COUNT(*)
FROM users
WHERE city = 'Beijing'
GROUP BY city;

CREATE INDEX idx_user_city ON users(city);