Nhóm với GROUP BY

Trong Mô-đun 2, bạn đã học rằng COUNT(*), AVG(...), SUM(...) giảm tất cả hàng thành một hàng kết quả đơn. Nhưng nếu bạn muốn có mức lương trung bình cho từng bộ phận, không phải cho toàn bộ công ty? Bạn muốn một hàng per nhóm. Đó là công việc của GROUP BY.

Cú pháp

SELECT grouping_column, AGGREGATE_FUNCTION(...)
FROM   table
GROUP BY grouping_column;

Đọc bằng tiếng Anh: "chia các hàng thành các nhóm có cùng giá trị grouping_column và tính tổng cho mỗi nhóm".

-- Average salary per department:
SELECT department_id, AVG(salary) AS average
FROM   employees
GROUP BY department_id;

Kết quả có một hàng cho mỗi department_id riêng biệt được tìm thấy trong employees. Bạn sẽ nhận được 5 hàng: một hàng cho mỗi phòng trong số 4 phòng ban (1, 2, 3, 4) cộng một cho NULL (Andrea Costa).

Với JOIN để có tên dễ đọc

Thường thì bạn muốn nhóm theo department_id nhưng hiển thị tên của khoa. Bạn kết hợp GROUP BY với JOIN:

SELECT d.name AS department,
       COUNT(*)    AS count,
       AVG(e.salary) AS average_salary
FROM   employees   AS e
JOIN   departments AS d ON e.department_id = d.id
GROUP BY d.name
ORDER BY count DESC;

Đây là một truy vấn kinh doanh thông minh chính thức: trong ba dòng cho bạn biết mỗi bộ phận có bao nhiêu nhân viên và họ kiếm được bao nhiêu tiền trung bình, sắp xếp từ lớn nhất đến nhỏ nhất.

Nguyên tắc vàng

Khi bạn sử dụng GROUP BY, mọi cột trong SELECT phải:

1. một trong các cột nhóm (ví dụ: department_id, d.name) hoặc
2. bên trong hàm tổng hợp (ví dụ: AVG(salary), COUNT(*)).

-- ERROR: first_name is neither in GROUP BY nor inside an aggregate
SELECT department_id, first_name, AVG(salary)
FROM   employees
GROUP BY department_id;

PostgreSQL trả về một lỗi rõ ràng ("cột phải xuất hiện trong GROUP BY mệnh đề hoặc được sử dụng trong hàm tổng hợp"). Đó là chuyện bình thường vào đầu tuần nhầm lẫn: nếu bạn nhìn thấy nó, hãy dừng lại và tự hỏi "giá trị của first_name cho nhóm 3 người?" — không có câu trả lời duy nhất và đó là lý do tại sao động cơ từ chối.

Hãy thử nó

Xem lại bài tập