Bài 8: GROUP BY: một kết quả cho mỗi nhóm

Báo cáo toàn câu lạc bộ cho cô Mai biết tổng số xu đang có. Khi chia hoạt động theo khối, cô cần biết riêng từng khối có bao nhiêu người và bao nhiêu xu. Viết một truy vấn riêng cho mỗi khối sẽ dài và dễ bỏ sót khối mới. Cô muốn chia học viên thành từng nhóm theo khối, rồi dùng cùng một công thức để tính cho từng nhóm.

Mỗi khối tạo một dòng báo cáo

Trong bảng học viên, cột grade cho biết khối lớp. Hai bạn cùng khối cần góp số xu vào cùng một kết quả. Trước khi viết truy vấn, hãy xác định: mỗi dòng kết quả sẽ cho biết thông tin của một khối lớp.

Gom nhóm (grouping) là chia các dòng thành từng nhóm có cùng giá trị ở cột được chọn. Mệnh đề GROUP BY xác định cột dùng để chia. Hàm tổng hợp sau đó tính riêng trên các dòng của mỗi nhóm:

SELECT grade, COUNT(*) AS n
FROM students
GROUP BY grade
ORDER BY grade;

Kết quả có bốn dòng, mang các khối 6, 7, 8 và 9. Khối 6 có An và Dung, nên n bằng 2. Khối 8 chỉ có Chi, nên số đếm của khối đó bằng 1.

Cột nhóm là cột quyết định các dòng có chung nhóm hay không. Ở đây, cột nhóm là grade, không phải name. Hai học viên khác tên vẫn ở chung nhóm nếu cùng khối lớp.

Hình dưới đây cho thấy sự thay đổi: bảng ban đầu có một dòng cho mỗi người, còn báo cáo có một dòng cho mỗi khối. Các học viên cùng khối được gom lại để tính chung, rồi xuất hiện trong một dòng kết quả:

Bạn có thể đặt nhiều hàm tổng hợp trong cùng truy vấn. Các hàm tính trên cùng những học viên trong nhóm, nhưng mỗi hàm trả lời một câu hỏi khác nhau. Câu tiếp theo thêm tổng xu vào số người:

SELECT grade, COUNT(*) AS n, SUM(coins) AS total
FROM students
GROUP BY grade
ORDER BY grade;

Khối 7 có hai người và tổng 240 xu, vì Bình có 90 còn Lan có 150. Khối 6 có tổng 180 xu từ An và Dung. Khối 8 cũng có 180 xu nhưng chỉ một người, nên tổng bằng nhau chưa có nghĩa số người bằng nhau.

GROUP BY không cam kết thứ tự trình bày. ORDER BY grade mới yêu cầu khối nhỏ đứng trước khối lớn. Nếu đề yêu cầu sắp xếp, hãy viết ORDER BY rõ ràng. Bạn vẫn cần làm vậy dù lần chạy hiện tại cho thứ tự có vẻ đúng.

Hai cột nhóm phân biệt từng cặp giá trị

Cô Mai muốn chia danh sách theo cả địa phương và khối lớp. Chỉ dùng city sẽ gộp mọi học viên cùng thành phố. Chỉ dùng grade lại gộp người từ nhiều địa phương khác nhau.

Khi GROUP BY có hai cột, một nhóm được xác định bằng cả cặp giá trị. Hai dòng chỉ chung nhóm nếu cả thành phố và khối đều giống nhau. Trước tiên, hãy nhìn cách chia chỉ theo thành phố:

SELECT city, COUNT(*) AS n
FROM students
GROUP BY city
ORDER BY city;

Hà Nội có ba học viên trong một dòng kết quả. An, Chi và Lan sống cùng thành phố nhưng học ba khối khác nhau. Vì vậy, con số 3 phù hợp với báo cáo theo địa phương, chưa đáp ứng báo cáo theo địa phương và khối.

Để giữ sự khác nhau về khối, hãy thêm grade vào phần nhóm và phần chọn cột. Truy vấn sau tạo một dòng riêng cho mỗi cặp thành phố và khối có học viên:

SELECT city, grade, COUNT(*) AS n
FROM students
GROUP BY city, grade
ORDER BY city, grade;

Kết quả có sáu dòng vì không cặp thành phố–khối nào lặp trong bộ mẫu. Số dòng bằng số người chỉ là trùng hợp. Trong bộ mẫu, không có hai học viên nào cùng thành phố và cùng khối lớp.

Nếu thêm một học viên Hà Nội khối 6, nhóm này sẽ có hai người. Báo cáo vẫn chỉ có một dòng cho nhóm Hà Nội khối 6.

Có thể hình dung hai cột nhóm như một địa chỉ. Cột city cho biết thành phố, còn grade cho biết khối lớp trong thành phố đó. Nếu bỏ một trong hai cột nhóm, báo cáo sẽ gộp cả những học viên bạn đang muốn phân biệt.

Giá trị NULL cũng tạo nhóm

Cô Mai cần đếm học viên theo người hướng dẫn. Một số bạn chưa được phân công, nên cột mentor là NULL. Với GROUP BY, những dòng có mentor là NULL được đặt vào cùng một nhóm.

Để thấy nhóm thiếu thông tin, hãy đếm dòng thay vì đếm ô mentor. Hai phép đếm trong câu sau giúp đối chiếu sự khác nhau:

SELECT mentor,
       COUNT(*) AS students_count,
       COUNT(mentor) AS named_count
FROM students
GROUP BY mentor
ORDER BY mentor;

Nhóm NULL có students_count bằng 2 vì gồm Bình và Dung. named_count bằng 0 vì cả hai ô mentor đều thiếu giá trị. Hai nhóm còn lại có tên Cô Mai và Thầy Nam, mỗi nhóm có hai người.

Hai kết quả khác nhau vì cách chia nhóm và cách đếm là hai bước riêng. GROUP BY gom những dòng có cùng giá trị người hướng dẫn. Sau đó, COUNT(mentor) chỉ đếm các ô có giá trị trong từng nhóm. Việc hàm đếm bỏ NULL không làm nhóm NULL biến mất.

Báo cáo này chia nhóm theo tên người hướng dẫn trong cột mentor. Nếu hai thầy cô trùng tên, họ sẽ bị gộp vào cùng một nhóm. Ở bài về khóa, bạn sẽ học cách dùng mã riêng để phân biệt những người trùng tên.

Chọn cột có ý nghĩa cho cả nhóm

Mỗi dòng báo cáo theo khối chứa thông tin của nhiều người. Cột grade có một giá trị chung, còn SUM(coins) tạo tổng chung. Nhưng name có thể có nhiều giá trị trong cùng nhóm.

Nếu thêm tên học viên vào báo cáo như dưới đây, bạn sẽ gặp một vấn đề. Một khối có nhiều học viên, nhưng truy vấn chưa quy định chọn tên ai để đặt cạnh tổng xu:

SELECT grade, name, SUM(coins) AS total
FROM students
GROUP BY grade
ORDER BY grade;

Trong khối 6 có cả An và Dung, nên một tên riêng không thể diễn đạt cả nhóm. SQLite vẫn chạy câu lệnh này, nhưng chạy được chưa có nghĩa là báo cáo trả lời đúng câu hỏi. Bạn nên chỉ chọn cột khối lớp và các kết quả tổng hợp cần có, chẳng hạn số người và tổng xu.

Thêm name vào GROUP BY sẽ đổi cách chia nhóm, nên chưa chắc sửa được báo cáo. Làm vậy sẽ chia nhỏ kết quả theo khối và tên, khiến một khối có thể xuất hiện nhiều lần. Nếu yêu cầu mỗi khối chỉ có một dòng, hãy giữ cách nhóm theo khối và bỏ cột tên khỏi SELECT.

WHERE thay đổi người đóng góp vào tổng

Báo cáo tiếp theo chỉ xét học viên có ít nhất 100 xu. Điều kiện này nói về mỗi người, nên đặt trong WHERE. Sau khi loại người chưa đủ ngưỡng, GROUP BY mới chia những dòng còn lại:

SELECT grade, COUNT(*) AS n, SUM(coins) AS total
FROM students
WHERE coins >= 100
GROUP BY grade
ORDER BY grade;

Khối 6 còn An nên tổng bằng 120, còn khối 7 chỉ còn Lan với 150 xu. Các số này là tổng của người đạt ngưỡng, không phải tổng toàn khối. Bạn cần nêu bộ lọc trong mô tả báo cáo để người đọc không hiểu nhầm.

Nếu bộ lọc không giữ dòng nào, sẽ không có nhóm nào để trả. Đây là điểm khác truy vấn tổng hợp toàn bảng ở bài 7. Hãy thử lọc một khối chưa có học viên rồi vẫn yêu cầu nhóm theo khối:

SELECT grade, COUNT(*) AS n
FROM students
WHERE grade = 12
GROUP BY grade;

Kết quả rỗng là đúng vì dữ liệu không chứa nhóm khối 12. COUNT(*) không tự tạo một dòng khối mới mang số 0. Nếu muốn lọc theo tổng của nhóm đang có, bạn sẽ dùng HAVING ở bài tiếp theo.

Lỗi thường gặp

Tóm tắt

• Với GROUP BY, mỗi dòng kết quả cho biết thông tin của một nhóm thay vì một học viên.
• Nhóm nhiều cột được xác định bằng cả bộ giá trị, không chỉ cột đầu tiên.
• Các dòng có giá trị nhóm NULL vẫn tạo một nhóm chung.
• Hãy chọn cột nhóm cùng các kết quả tổng hợp mô tả cả nhóm, như số người và tổng xu.
• WHERE chọn học viên trước khi tính tổng hợp. ORDER BY quy định thứ tự các dòng kết quả.

Kiểm tra nhanh

Đọc thêm

• SQLite mô tả GROUP BY, NULL và cột tổng hợp.
• SQLite mô tả cách COUNT xử lý NULL và DISTINCT.