Aggregation & GROUP BY

Aggregate Functions

SELECT
    COUNT(*) as total_users,
    COUNT(DISTINCT city) as unique_cities,
    AVG(age) as avg_age,
    MIN(created_at) as first_signup,
    MAX(salary) as highest_salary,
    SUM(revenue) as total_revenue
FROM users;

GROUP BY

Nhóm rows theo column, tính aggregate cho mỗi nhóm:

-- Đếm users theo thành phố
SELECT city, COUNT(*) as user_count
FROM users
GROUP BY city
ORDER BY user_count DESC;

-- Doanh thu theo tháng
SELECT
    DATE_TRUNC('month', created_at) as month,
    SUM(total) as revenue,
    COUNT(*) as order_count,
    AVG(total) as avg_order_value
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

HAVING — Filter sau GROUP BY

-- Chỉ lấy thành phố có > 100 users
SELECT city, COUNT(*) as user_count
FROM users
GROUP BY city
HAVING COUNT(*) > 100
ORDER BY user_count DESC;

Thứ tự thực thi SQL

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Key insight: WHERE filter trước GROUP BY (trên raw rows), HAVING filter sau GROUP BY (trên grouped results). Đây là lý do không dùng alias trong WHERE.