SQL từ cơ bản đến nâng cao
Trang chủ / Module 4
📊 Module 4 / 16 · 5-6 giờ

Hàm tổng hợp và GROUP BY

Biến hàng nghìn dòng thành con số báo cáo: COUNT/SUM/AVG, GROUP BY, HAVING, FILTER, thống kê phân vị — kèm các lỗi sai số liệu kinh điển.

GROUP BYHAVINGFILTERAggregate
📌 Vì sao module này quan trọng

Phần lớn công việc "thực tế" với SQL là tổng hợp: doanh thu theo tháng, số khách theo thành phố, tỉ lệ huỷ đơn. Đây cũng là nơi sinh ra số liệu sai nhưng trông rất hợp lý: COUNT(*) vs COUNT(cột), trung bình của trung bình, SUM bị nhân đôi sau JOIN. Hiểu sâu ở đây là phân biệt người "viết được query" với người "tin được số liệu của mình".

🎯 Mục tiêu học tập

  • Phân biệt COUNT(*), COUNT(col), COUNT(DISTINCT col) và hành vi của hàm tổng hợp với NULL
  • Dùng GROUP BY đa cột/biểu thức; phân biệt WHERE và HAVING
  • Dùng FILTER và CASE để tổng hợp có điều kiện (pivot), string_agg/array_agg để gộp chuỗi
  • Tính trung bình có trọng số, phân vị (median, p95) và tỉ lệ phần trăm đúng cách

Các hàm tổng hợp cốt lõi và cách chúng đối xử với NULL

Hàm tổng hợp gom nhiều dòng thành một giá trị. Quy tắc vàng: mọi hàm tổng hợp (trừ COUNT(*)) bỏ qua NULL.

SELECT count(*)              AS tong_dong,
       count(city)           AS co_thanh_pho,
       count(DISTINCT city)  AS so_thanh_pho_khac_nhau,
       min(created_at)       AS som_nhat,
       max(created_at)       AS muon_nhat
FROM customers;
Kết quả
 tong_dong | co_thanh_pho | so_thanh_pho_khac_nhau |        som_nhat        |       muon_nhat
-----------+--------------+------------------------+------------------------+------------------------
        60 |           56 |                      5 | 2024-01-04 09:00:00+07 | 2024-06-29 09:00:00+07
(1 row)
SELECT sum(qty * unit_price)                       AS doanh_thu_gop,
       round(avg(qty), 3)                           AS qty_trung_binh,
       sum(qty)                                     AS tong_so_luong,
       count(*)                                     AS so_dong_chi_tiet,
       count(DISTINCT order_id)                     AS so_don_co_hang
FROM order_items;
Kết quả
 doanh_thu_gop  | qty_trung_binh | tong_so_luong | so_dong_chi_tiet | so_don_co_hang
----------------+----------------+---------------+------------------+----------------
 13008973000.00 |          2.001 |          2001 |             1000 |            400
(1 row)
❌ Lỗi thường gặp — AVG bỏ qua NULL — kết quả lệch mà không báo

Nếu 30% đơn chưa giao có shipped_at NULL, avg(shipped_at - ordered_at) chỉ tính trên đơn đã giao — thường là điều bạn muốn, nhưng phải biết mình đang làm vậy. Ngược lại, sum(x) trên tập rỗng trả NULL chứ không phải 0: dùng COALESCE(sum(x), 0) khi hiển thị. Còn count() trên tập rỗng trả 0.

SELECT sum(amount) AS tong, COALESCE(sum(amount), 0) AS tong_an_toan, count(*) AS so_dong
FROM payments WHERE method = 'khong_ton_tai';
Kết quả
 tong | tong_an_toan | so_dong
------+--------------+---------
 NULL |            0 |       0
(1 row)

GROUP BY: gom nhóm

Quy tắc: mọi cột trong SELECT không nằm trong hàm tổng hợp phải có mặt trong GROUP BY (hoặc phụ thuộc hàm vào khoá chính được gom). Mỗi tổ hợp giá trị duy nhất của các cột GROUP BY tạo một dòng kết quả.

SELECT status, count(*) AS so_don
FROM orders
GROUP BY status
ORDER BY so_don DESC;
Kết quả
  status   | so_don
-----------+--------
 delivered |    240
 paid      |     40
 pending   |     40
 cancelled |     40
 shipped   |     40
(5 rows)
-- Nhóm theo biểu thức: doanh thu theo tháng (múi giờ VN)
SELECT date_trunc('month', o.ordered_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date AS thang,
       count(DISTINCT o.id)                    AS so_don,
       round(sum(oi.qty * oi.unit_price * (100 - oi.discount_pct) / 100)) AS doanh_thu
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY 1
ORDER BY 1;
Kết quả
   thang    | so_don | doanh_thu
------------+--------+------------
 2024-01-01 |      1 |   30429000
 2024-02-01 |     21 | 1008038600
 2024-03-01 |     26 |  644785000
 2024-04-01 |     39 | 1061789700
 2024-05-01 |     61 | 2017102000
 2024-06-01 |     69 | 2353363000
 2024-07-01 |     60 | 2129170500
 2024-08-01 |     37 | 1283365000
 2024-09-01 |     22 |  805237000
 2024-10-01 |     20 |  768568000
 2024-11-01 |      4 |  122801200
(11 rows)
SELECT city, id FROM customers GROUP BY city;
Lỗi PostgreSQL trả về
ERROR:  column "customers.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT city, id FROM customers GROUP BY city;
                     ^
❌ Lỗi thường gặp — Lỗi 'must appear in the GROUP BY clause'

Hiểu đúng thay vì chữa bừa: sau khi gom theo city, mỗi nhóm có nhiều id — database không biết chọn id nào. Có 3 hướng đúng: (1) thêm cột vào GROUP BY nếu bạn thật sự muốn nhóm mịn hơn; (2) bọc bằng hàm tổng hợp (min(id), array_agg(id)); (3) nếu cần "dòng đại diện" mỗi nhóm → dùng DISTINCT ON hoặc window function (module 8). Đừng thêm mọi cột vào GROUP BY cho hết lỗi — bạn vừa đổi grain mà không hay.

🧠 Cách chuyên gia — GROUP BY theo vị trí cột

GROUP BY 1, 2 (số thứ tự cột SELECT) tiện khi nhóm theo biểu thức dài, dùng thoải mái cho phân tích ad-hoc; trong code dài hạn nên dùng alias hoặc lặp biểu thức để khi ai đó chèn cột vào SELECT không vỡ ngầm.

WHERE hay HAVING?

WHERE lọc dòng trước khi gom (dùng được index, giảm dữ liệu sớm). HAVING lọc nhóm sau khi gom (chỉ dùng cho điều kiện trên hàm tổng hợp). Quy tắc chuyên gia: điều kiện nào không cần kết quả tổng hợp thì đặt ở WHERE — vừa nhanh hơn, vừa rõ nghĩa.

-- Khách có từ 8 đơn trở lên (không tính đơn huỷ)
SELECT customer_id, count(*) AS so_don, max(ordered_at)::date AS don_gan_nhat
FROM orders
WHERE status <> 'cancelled'        -- lọc dòng: ở WHERE
GROUP BY customer_id
HAVING count(*) >= 8               -- lọc nhóm: ở HAVING
ORDER BY so_don DESC, customer_id
LIMIT 8;
Kết quả
 customer_id | so_don | don_gan_nhat
-------------+--------+--------------
           8 |      8 | 2024-06-13
          10 |      8 | 2024-07-10
          12 |      8 | 2024-07-19
          22 |      8 | 2024-07-25
          24 |      8 | 2024-07-05
          26 |      8 | 2024-08-03
          36 |      8 | 2024-08-15
          38 |      8 | 2024-09-02
(8 rows)
❌ Lỗi thường gặp — Đặt điều kiện dòng trong HAVING

GROUP BY status HAVING status = 'paid' chạy đúng nhưng database có thể gom toàn bộ dữ liệu rồi mới bỏ nhóm; đặt WHERE status = 'paid' lọc trước khi gom. (PostgreSQL đôi khi tự đẩy xuống, nhưng đừng dựa vào đó và nó làm ý định khó đọc.)

FILTER và CASE: tổng hợp có điều kiện (pivot)

Một lần quét bảng có thể tính nhiều chỉ số theo điều kiện khác nhau. FILTER (WHERE ...) (chuẩn SQL, PostgreSQL) gọn hơn CASE bên trong hàm tổng hợp; hai cách tương đương.

SELECT
  count(*)                                         AS tong_don,
  count(*) FILTER (WHERE status = 'delivered')     AS da_giao,
  count(*) FILTER (WHERE status = 'cancelled')     AS da_huy,
  round(100.0 * count(*) FILTER (WHERE status = 'cancelled') / count(*), 1) AS ti_le_huy_pct
FROM orders;
Kết quả
 tong_don | da_giao | da_huy | ti_le_huy_pct
----------+---------+--------+---------------
      400 |     240 |     40 |          10.0
(1 row)
-- Pivot: mỗi hàng 1 thành phố, mỗi cột 1 trạng thái đơn
SELECT COALESCE(c.city, 'Chưa rõ') AS thanh_pho,
       count(*) FILTER (WHERE o.status = 'delivered') AS delivered,
       count(*) FILTER (WHERE o.status = 'shipped')   AS shipped,
       count(*) FILTER (WHERE o.status = 'cancelled') AS cancelled
FROM orders o JOIN customers c ON c.id = o.customer_id
GROUP BY 1 ORDER BY 1;
Kết quả
 thanh_pho | delivered | shipped | cancelled
-----------+-----------+---------+-----------
 Cần Thơ   |        45 |       8 |         8
 Chưa rõ   |        17 |       2 |         4
 Đà Nẵng   |        46 |       7 |         7
 Hà Nội    |        45 |       8 |         7
 Hải Phòng |        44 |       6 |         6
 TP.HCM    |        43 |       9 |         8
(6 rows)
-- Cách tương đương, chạy được mọi DBMS: CASE trong hàm tổng hợp
SELECT sum(CASE WHEN method = 'card' THEN amount ELSE 0 END) AS card,
       sum(CASE WHEN method = 'momo' THEN amount ELSE 0 END) AS momo,
       sum(CASE WHEN method = 'cod'  THEN amount ELSE 0 END) AS cod
FROM payments;
Kết quả
     card      |     momo      |      cod
---------------+---------------+---------------
 3614132000.00 | 3937044200.00 | 3084502800.00
(1 row)
🧠 Cách chuyên gia — Một lần quét, nhiều chỉ số

Thay vì viết 5 truy vấn count(*) với 5 điều kiện (5 lần quét bảng), viết 1 truy vấn với 5 FILTER. Với bảng triệu dòng đây là khác biệt 5 lần thời gian. Đây cũng là cách dựng dashboard "KPI" trong một câu lệnh.

Gộp giá trị: string_agg, array_agg, thống kê nâng cao

SELECT c.name AS danh_muc,
       count(*) AS so_sp,
       string_agg(p.name, ', ' ORDER BY p.price DESC) AS cac_san_pham
FROM products p JOIN categories c ON c.id = p.category_id
GROUP BY c.name ORDER BY c.name;
Kết quả
  danh_muc  | so_sp |                             cac_san_pham
------------+-------+-----------------------------------------------------------------------
 Áo         |     3 | Áo khoác gió, Áo sơ mi công sở, Áo thun basic
 Điện thoại |     3 | Điện thoại Nova Pro, Điện thoại Aurora X1, Điện thoại Aurora Lite
 Gia dụng   |     2 | Robot hút bụi, Quạt đứng cũ (ngừng bán)
 Giày       |     2 | Giày da nam, Giày chạy bộ Sprint
 Laptop     |     3 | Laptop Zenbook 16 Pro, Laptop Zenbook 14, Laptop Student 15
 Nhà bếp    |     3 | Nồi chiên không dầu 5L, Máy xay sinh tố, Bộ dao nhà bếp 6 món
 Phụ kiện   |     4 | Tai nghe không dây Echo, Sạc nhanh 65W, Chuột không dây, Cáp USB-C 1m
(7 rows)
SELECT percentile_cont(0.5)  WITHIN GROUP (ORDER BY price) AS trung_vi_gia,
       round(percentile_cont(0.9) WITHIN GROUP (ORDER BY price)) AS p90_gia,
       round(avg(price))                                    AS trung_binh,
       round(stddev_samp(price))                            AS do_lech_chuan
FROM products WHERE is_active;
Kết quả
 trung_vi_gia | p90_gia  | trung_binh | do_lech_chuan
--------------+----------+------------+---------------
      1740000 | 20043000 |    6983222 |       9794210
(1 row)
-- Thời gian trung bình từ đặt đến giao (chỉ đơn đã giao)
SELECT round(avg(extract(epoch FROM shipped_at - ordered_at) / 3600), 1) AS gio_trung_binh,
       count(shipped_at) AS so_don_da_giao
FROM orders;
Kết quả
 gio_trung_binh | so_don_da_giao
----------------+----------------
           71.7 |            280
(1 row)
❌ Lỗi thường gặp — Trung bình của các trung bình

Trung bình giá trị đơn của từng thành phố rồi lấy trung bình các thành phố không bằng trung bình toàn bộ đơn (mỗi thành phố có số đơn khác nhau). Phải tính từ dữ liệu gốc hoặc dùng trung bình có trọng số: sum(giá_trị) / sum(số_lượng). Tương tự, tỉ lệ không được cộng/trung bình trực tiếp — hãy cộng tử số và mẫu số riêng rồi mới chia ở bước cuối.

❌ Lỗi thường gặp — Mean che giấu đuôi dữ liệu

Thời gian giao hàng trung bình 36 giờ nghe ổn, nhưng 5% khách chờ 10 ngày. Báo cáo hiệu năng/độ trễ dùng median và p95/p99, không chỉ mean (percentile_cont).

Bẫy lớn nhất: JOIN rồi tổng hợp (fan-out)

Đơn hàng có nhiều dòng chi tiết. Nếu JOIN orders với order_items rồi SUM một cột thuộc orders (hoặc COUNT(*) để đếm đơn), mỗi đơn bị nhân lên theo số dòng chi tiết. Đây là nguồn số liệu sai số một của báo cáo doanh thu.

SELECT count(*)                  AS dem_dong_sau_join,
       count(DISTINCT o.id)       AS so_don_that
FROM orders o
JOIN order_items oi ON oi.order_id = o.id;
Kết quả
 dem_dong_sau_join | so_don_that
-------------------+-------------
              1000 |         400
(1 row)
-- payments có grain 1 dòng / đơn; order_items có grain n dòng / đơn.
-- SAI: nối cả hai rồi SUM(payments.amount) => bị nhân đôi/ba/tư theo số dòng items
SELECT sum(p.amount) AS tong_sai
FROM payments p JOIN order_items oi ON oi.order_id = p.order_id;
Kết quả
    tong_sai
----------------
 32187028400.00
(1 row)
-- ĐÚNG: tổng hợp từng nhánh về cùng grain (1 dòng/đơn) rồi mới nối, hoặc chỉ SUM ở bảng gốc
SELECT sum(amount) AS tong_dung FROM payments;
Kết quả
   tong_dung
----------------
 10635679000.00
(1 row)
🧠 Cách chuyên gia — Tổng hợp trước, nối sau

Khi cần ghép số liệu từ nhiều bảng "một-nhiều" khác nhau, tổng hợp mỗi bảng về cùng grain trong subquery/CTE trước, rồi mới JOIN. Luôn kiểm tra chéo: tổng sau khi JOIN phải bằng tổng của bảng gốc. Sẽ nói kỹ hơn ở module 5 và 7.

🏋️ Bài tập thực hành

Doanh thu theo phương thức thanh toánDễ

Với bảng payments, cho biết mỗi method: số giao dịch, tổng tiền, giá trị trung bình (làm tròn 0 chữ số), sắp theo tổng tiền giảm dần.

Xem lời giải & giải thích
SELECT method, count(*) AS so_gd, sum(amount) AS tong_tien, round(avg(amount)) AS tb
FROM payments GROUP BY method ORDER BY tong_tien DESC;
Kết quả
 method | so_gd |   tong_tien   |    tb
--------+-------+---------------+----------
 momo   |   108 | 3937044200.00 | 36454113
 card   |   106 | 3614132000.00 | 34095585
 cod    |   106 | 3084502800.00 | 29099083
(3 rows)
Top khách hàng chi tiêuTrung bình

Tính tổng tiền đã thanh toán (từ payments join orders) theo khách hàng; chỉ hiện khách có tổng > 150 triệu, sắp giảm dần.

Xem lời giải & giải thích
SELECT o.customer_id, count(*) AS so_don_tra_tien, sum(p.amount) AS tong_chi
FROM payments p JOIN orders o ON o.id = p.order_id
GROUP BY o.customer_id
HAVING sum(p.amount) > 150000000
ORDER BY tong_chi DESC;
Kết quả
 customer_id | so_don_tra_tien |   tong_chi
-------------+-----------------+--------------
          38 |               7 | 388803000.00
          50 |               7 | 362434000.00
          26 |               6 | 357144600.00
           8 |               6 | 335643100.00
          28 |               6 | 280513000.00
          32 |               6 | 274733000.00
          52 |               6 | 271006000.00
          20 |               5 | 256363000.00
          40 |               6 | 255182000.00
          16 |               6 | 248593300.00
... (cắt bớt, chạy trên máy bạn để xem đủ)

Điều kiện trên sum() phải ở HAVING. Không cần DISTINCT vì mỗi đơn chỉ có một dòng payment (đúng grain).

Tỉ lệ huỷ đơn theo thángTrung bình

Với mỗi tháng đặt hàng (giờ VN), tính số đơn, số đơn huỷ và tỉ lệ huỷ phần trăm (1 chữ số thập phân).

Xem lời giải & giải thích
SELECT date_trunc('month', ordered_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date AS thang,
       count(*) AS so_don,
       count(*) FILTER (WHERE status = 'cancelled') AS so_huy,
       round(100.0 * count(*) FILTER (WHERE status = 'cancelled') / count(*), 1) AS ti_le_huy
FROM orders GROUP BY 1 ORDER BY 1;
Kết quả
   thang    | so_don | so_huy | ti_le_huy
------------+--------+--------+-----------
 2024-01-01 |      1 |      0 |       0.0
 2024-02-01 |     22 |      1 |       4.5
 2024-03-01 |     38 |     12 |      31.6
 2024-04-01 |     41 |      2 |       4.9
 2024-05-01 |     68 |      7 |      10.3
 2024-06-01 |     75 |      6 |       8.0
 2024-07-01 |     62 |      2 |       3.2
 2024-08-01 |     40 |      3 |       7.5
 2024-09-01 |     24 |      2 |       8.3
 2024-10-01 |     25 |      5 |      20.0
 2024-11-01 |      4 |      0 |       0.0
(11 rows)

Nhân 100.0 (số thực) trước khi chia để tránh chia nguyên. Tính tỉ lệ bằng tử/mẫu ở cùng nhóm — không trung bình các tỉ lệ.

Tìm lỗi nhân đôi doanh thuKhó

Một đồng nghiệp tính tổng giá trị đơn bằng SELECT sum(p.amount) FROM payments p JOIN order_items oi ON oi.order_id = p.order_id. Chứng minh con số này khác SELECT sum(amount) FROM payments và giải thích vì sao. Viết lại một truy vấn đúng đưa ra, theo từng đơn, tổng tiền hàng từ items và số tiền payment cạnh nhau.

Xem lời giải & giải thích
WITH it AS (
  SELECT order_id, round(sum(qty * unit_price * (100 - discount_pct) / 100), 2) AS tien_hang
  FROM order_items GROUP BY order_id
)
SELECT p.order_id, it.tien_hang, p.amount, it.tien_hang = p.amount AS khop
FROM payments p JOIN it ON it.order_id = p.order_id
ORDER BY p.order_id
LIMIT 5;
Kết quả
 order_id |  tien_hang  |   amount    | khop
----------+-------------+-------------+------
        1 |  2650000.00 |  2650000.00 | t
        2 | 69630000.00 | 69630000.00 | t
        3 | 37866000.00 | 37866000.00 | t
        4 |  1197000.00 |  1197000.00 | t
        5 |  1590000.00 |  1590000.00 | t
(5 rows)

Mỗi đơn có 1 payment nhưng 1–4 dòng items; JOIN trực tiếp nhân payment lên theo số dòng. Cách đúng: gom items về grain 'đơn' trong CTE rồi mới nối. Việc thấy cột khop đều TRUE còn là phép kiểm tra chéo dữ liệu.

📚 Tài nguyên học tập

✅ Tự đánh giá hoàn thành

  • Giải thích được COUNT(*) khác COUNT(col) khác COUNT(DISTINCT col)
  • Đặt điều kiện dòng ở WHERE, điều kiện trên hàm tổng hợp ở HAVING
  • Dùng FILTER để tính nhiều chỉ số trong một lần quét
  • Nhận diện và tránh được lỗi fan-out (SUM bị nhân đôi sau JOIN) bằng cách tổng hợp trước, nối sau