Bài 4: Aggregate & GROUP BY

Bài 3 ghép nhiều bảng lại thành 1 tập kết quả lớn. Bài này làm ngược lại: gộp nhiều hàng thành 1 con số duy nhất — tổng doanh thu, số lượng khách hàng, giá trị đơn trung bình. Đây là nhóm câu hỏi kinh doanh phổ biến nhất trong thực tế ("doanh thu tháng này bao nhiêu?", "danh mục nào bán chạy nhất?"), và GROUP BY + hàm tổng hợp (aggregate function) là công cụ duy nhất trong SQL trả lời được. Bài này cũng đi sâu vào 1 cạm bẫy SQLite gây khó chịu nhất cho người quen PostgreSQL/MySQL: SQLite không ép buộc quy tắc "mọi cột SELECT phải nằm trong GROUP BY hoặc trong hàm tổng hợp" mà nhiều engine khác ép buộc nghiêm ngặt.

1. Hàm Tổng Hợp: Gộp Nhiều Hàng Thành 1 Giá Trị

SQLite có 5 hàm tổng hợp cốt lõi: COUNT, SUM, AVG, MIN, MAX. Không có GROUP BY, chúng gộp toàn bộ bảng thành đúng 1 hàng kết quả.

Tình huống 1 — Đếm hàng: 3 cách viết COUNT hoàn toàn khác nghĩa:

SELECT
  COUNT(*) AS total_orders,          -- đếm MỌI hàng, kể cả cột NULL
  COUNT(customer_id) AS with_customer, -- đếm hàng có customer_id KHÔNG NULL
  COUNT(DISTINCT customer_id) AS unique_customers -- đếm số khách hàng KHÁC NHAU
FROM orders;

Chạy thử: COUNT(*) trả về 28 (tổng số đơn), nhưng COUNT(customer_id) chỉ trả về 24 — vì 4 đơn khách vãng lai có customer_id IS NULL (đúng như đã xác nhận ở Bài 3) và COUNT(column) luôn bỏ qua giá trị NULL, chỉ COUNT(*) mới đếm mọi hàng vô điều kiện. Đây là điểm khác biệt bị hiểu nhầm nhiều nhất giữa COUNT(*) và COUNT(1 cột cụ thể).

Tình huống 2 — Doanh thu & giá trị đơn trung bình:

SELECT
  SUM(total_amount) AS total_revenue,
  AVG(total_amount) AS avg_order_value,
  MIN(total_amount) AS smallest_order,
  MAX(total_amount) AS largest_order
FROM orders;

AVG(total_amount) ở đây ra khoảng 128.27 — tính trên toàn bộ 28 đơn, bất kể trạng thái (kể cả đơn cancelled/refunded). Với báo cáo thực tế, thường cần lọc trước bằng WHERE:

SELECT AVG(total_amount) AS avg_delivered_value
FROM orders
WHERE status = 'delivered';

Kết quả tăng lên ~140.01 — cao hơn mức trung bình toàn bộ, vì loại bỏ các đơn cancelled/refunded vốn có xu hướng giá trị thấp hơn trong dataset này. Đây là ví dụ thực tế cho thấy WHERE lọc trước khi hàm tổng hợp tính toán — thứ tự này sẽ quan trọng hơn nữa khi so sánh với HAVING ở mục 5.

Tình huống 3 — Đếm khách hàng đang hoạt động (đếm theo điều kiện):

SELECT
  COUNT(*) AS total_customers,
  SUM(is_active) AS active_customers
FROM customers;

Kết quả: 12 khách hàng tổng, 10 đang hoạt động. Mẹo hay: SUM(is_active) hoạt động được vì is_active lưu 0/1 — cộng dồn 1 cột toàn giá trị 0/1 chính là đếm số hàng có giá trị 1. Cách viết tổng quát hơn (dùng được cho mọi điều kiện, không chỉ cột 0/1 sẵn có) là SUM(CASE WHEN is_active = 1 THEN 1 ELSE 0 END), hoặc gọn hơn trong SQLite: COUNT(*) FILTER (WHERE is_active = 1).

2. GROUP BY: Gộp Nhóm Theo 1 Hoặc Nhiều Cột

GROUP BY cột chia bảng thành các nhóm có cùng giá trị ở cột, rồi hàm tổng hợp tính riêng cho từng nhóm thay vì toàn bảng.

Tình huống 1 — Doanh thu theo danh mục sản phẩm (đã thấy ở Bài 3, giờ mở rộng):

SELECT p.category,
       SUM(oi.quantity * oi.unit_price) AS revenue,
       COUNT(DISTINCT oi.order_id) AS num_orders
FROM order_items oi
INNER JOIN products p ON oi.product_id = p.product_id
GROUP BY p.category
ORDER BY revenue DESC;

Kết quả 4 hàng, mỗi hàng 1 danh mục: Electronics 1633.87 (9 đơn), Office 1117.94 (5 đơn), Accessories 556.83 (9 đơn), Home Appliances 282.97 (6 đơn). Lưu ý COUNT(DISTINCT oi.order_id) chứ không phải COUNT(*) — vì 1 đơn hàng có thể có nhiều dòng order_items cùng danh mục, COUNT(*) sẽ đếm trùng.

Tình huống 2 — Số đơn & doanh thu theo trạng thái:

SELECT status, COUNT(*) AS num_orders, SUM(total_amount) AS total_revenue
FROM orders
GROUP BY status
ORDER BY num_orders DESC;

Kết quả: delivered chiếm áp đảo với 19/28 đơn (2660.21), các trạng thái còn lại (shipped, refunded, pending, cancelled) chỉ 2 đơn mỗi loại, processing đúng 1 đơn. Đây là kiểu báo cáo "phân bố trạng thái" cực kỳ phổ biến trong dashboard vận hành.

Tình huống 3 — Nhóm theo nhiều cột cùng lúc (compound grouping): phân tích trạng thái đơn hàng theo từng quốc gia.

SELECT c.country, o.status, COUNT(*) AS cnt
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.country, o.status
ORDER BY c.country, o.status;

GROUP BY c.country, o.status tạo ra 1 nhóm cho mỗi tổ hợp duy nhất của cả 2 cột — không phải nhóm theo country rồi lại nhóm theo status một cách độc lập. Chạy thử sẽ thấy Việt Nam xuất hiện ở nhiều hàng (mỗi trạng thái 1 hàng riêng), trong khi các quốc gia chỉ có 1-2 đơn thường chỉ có 1-2 hàng.

3. Cạm Bẫy: Cột Không GROUP BY, Không Tổng Hợp

Theo chuẩn SQL, mọi cột xuất hiện trong SELECT của 1 câu lệnh có GROUP BY phải là: (a) cột nằm trong GROUP BY, hoặc (b) cột nằm trong 1 hàm tổng hợp. PostgreSQL/MySQL (từ 5.7.5+) ép buộc nghiêm ngặt quy tắc này — vi phạm sẽ bị từ chối với lỗi cú pháp. SQLite thì không.

-- Hợp lệ trong SQLite, nhưng PostgreSQL sẽ TỪ CHỐI với lỗi:
-- "column "products.product_name" must appear in the GROUP BY clause..."
SELECT category, product_name, COUNT(*) AS num_products
FROM products
GROUP BY category;

Chạy thử trong sân chơi bên dưới: SQLite trả về đủ 4 hàng, mỗi hàng có 1 product_name "trông có vẻ hợp lý" — nhưng đó chỉ là hàng SQLite tình cờ quét thấy trước tiên trong mỗi nhóm (thường là hàng có product_id nhỏ nhất do thứ tự lưu trữ vật lý, nhưng đây không phải hành vi được đảm bảo theo chuẩn — không nên dựa vào nó). Không có mối liên hệ logic nào giữa product_name hiển thị và COUNT(*) đứng cạnh nó.

4. HAVING vs WHERE: Lọc Trước vs Sau Khi Gộp Nhóm

WHERE lọc từng hàng thô trước khi gộp nhóm — không dùng được với kết quả hàm tổng hợp (vì lúc WHERE chạy, hàm tổng hợp chưa tính xong, xem lại callout mục 2). HAVING lọc từng nhóm sau khi đã tổng hợp — dùng được điều kiện trên kết quả SUM/COUNT/AVG.

Tình huống 1 — Chỉ giữ danh mục có doanh thu > 300:

SELECT p.category, SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
INNER JOIN products p ON oi.product_id = p.product_id
GROUP BY p.category
HAVING revenue > 300
ORDER BY revenue DESC;

Kết quả chỉ còn 3/4 danh mục — Home Appliances (doanh thu 282.97, thấp hơn ngưỡng 300) bị loại khỏi báo cáo. Thử đổi HAVING thành WHERE revenue > 300 (giữ nguyên vị trí, tức là đặt trước GROUP BY) sẽ báo lỗi ngay — revenue là alias của kết quả SUM(), chưa tồn tại ở thời điểm WHERE chạy.

Tình huống 2 — Chỉ giữ danh mục thu hút từ 5 khách hàng khác nhau trở lên (đo độ phổ biến, không phải doanh thu):

SELECT p.category, COUNT(DISTINCT o.customer_id) AS num_customers
FROM order_items oi
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN orders o ON oi.order_id = o.order_id
GROUP BY p.category
HAVING num_customers >= 5
ORDER BY num_customers DESC;

Kết quả: Electronics (7 khách), Accessories (6 khách), Office (5 khách) — Home Appliances (chỉ 3 khách khác nhau) bị loại, dù doanh thu của nó không phải tiêu chí ở truy vấn này.

Tình huống 3 — Kết hợp cả 2: lọc hàng thô trước, rồi lọc nhóm sau: chỉ tính đơn delivered, rồi chỉ giữ khách có tổng chi tiêu > 200.

SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
WHERE status = 'delivered'
GROUP BY customer_id
HAVING total_spent > 200
ORDER BY total_spent DESC;

WHERE status = 'delivered' loại bỏ đơn không phải delivered trước khi gộp nhóm theo khách — nếu 1 khách có 1 đơn 500$ đã bị hủy và 1 đơn 50$ đã giao, WHERE đảm bảo chỉ 50$ được tính vào tổng, không phải 550$. HAVING total_spent > 200 sau đó lọc tiếp theo tổng đã tính đúng.

Bảng So Sánh WHERE vs HAVING

Sân chơi tương tác: SQL Workbench Với Biểu Đồ Cột Tự Động

Chạy bất kỳ query nào trả về đúng 2 cột (1 cột nhãn + 1 cột số) — workbench sẽ tự động vẽ thêm biểu đồ cột ngay bên dưới bảng kết quả, không cần bấm nút riêng. Thử ngay ví dụ đầu tiên (doanh thu theo danh mục) để thấy biểu đồ xuất hiện.

Đang tải...

⬇ Tải file schema + dữ liệu mẫu TechMart (.sql) — chạy được trực tiếp bằng sqlite3 CLI hoặc DB Browser for SQLite trên máy bạn.