COUNTIF, SUMIF và tổng hợp có điều kiện

Bạn sẽ học được

• Chuyển COUNTIF, SUMIF, AVERAGEIF sang SQL.
• Dùng FILTER và CASE để tính nhiều chỉ số trong một truy vấn.
• Phân biệt đếm dòng với đếm giá trị khác nhau.
• Tránh lỗi do NULL trong điều kiện.
• Xây bảng KPI giống vùng công thức tóm tắt trong Excel.

Khái niệm cốt lõi

Excel có bộ hàm có điều kiện; SQL giải bằng cách lọc rồi tổng hợp.

Trong database không có FILTER, dùng SUM(CASE WHEN ... THEN x ELSE 0 END) và COUNT(CASE WHEN ... THEN 1 END). Cú pháp dài hơn nhưng chạy được ở mọi nơi.

Ví dụ: bảng KPI theo thành phố

Giả sử Excel có bảng tóm tắt: mỗi thành phố một dòng, các cột là số đơn, doanh thu, giá trị đơn trung bình. SQL tương đương:

SELECT c.city,
       COUNT(DISTINCT o.id) AS so_don,
       SUM(oi.quantity * oi.unit_price) AS doanh_thu,
       SUM(oi.quantity * oi.unit_price) / COUNT(DISTINCT o.id) AS gia_tri_don_tb
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'completed'
GROUP BY c.city
ORDER BY doanh_thu DESC;

Điểm cần chú ý: COUNT(DISTINCT o.id) chứ không phải COUNT(*), vì JOIN với order_items nhân dòng. Đây là khác biệt lớn so với COUNTIF trên một cột phẳng trong Excel — SQL phải tự bảo vệ khỏi nhân dòng.

Công thức gia_tri_don_tb cũng phải chia cho số đơn, không chia cho số dòng hàng. AVG(oi.quantity * oi.unit_price) cho giá trị trung bình mỗi dòng sản phẩm, thường nhỏ hơn và không phải điều báo cáo muốn.

Nhiều chỉ số trong một truy vấn

Ví dụ cần đếm số đơn theo từng trạng thái trên cùng một dòng thành phố:

SELECT c.city,
       COUNT(*) FILTER (WHERE o.status = 'completed') AS don_hoan_thanh,
       COUNT(*) FILTER (WHERE o.status = 'pending') AS don_cho,
       COUNT(*) FILTER (WHERE o.status = 'cancelled') AS don_huy
FROM orders o
JOIN customers c ON c.id = o.customer_id
GROUP BY c.city;

Mỗi FILTER chỉ đếm dòng thỏa điều kiện, các cột còn lại vẫn giữ. Cách này thay cho ba lần chạy COUNTIF và ghép kết quả.

Bản tương thích rộng hơn:

SELECT c.city,
       SUM(CASE WHEN o.status = 'completed' THEN 1 ELSE 0 END) AS don_hoan_thanh,
       SUM(CASE WHEN o.status = 'pending' THEN 1 ELSE 0 END) AS don_cho
FROM orders o
JOIN customers c ON c.id = o.customer_id
GROUP BY c.city;

Với COUNT, có thể viết COUNT(CASE WHEN ... THEN 1 END) — NULL trong nhánh ELSE không được đếm.

Đếm dòng và đếm giá trị

Ba cách đếm cho ba câu hỏi khác nhau:

Ví dụ customers có 12 khách, 2 khách thiếu email:

• COUNT(*) = 12.
• COUNT(email) = 10.
• COUNT(DISTINCT city) = 5 nếu có 5 thành phố.

Trong Excel, COUNTA gần với COUNT(cot) và COUNTBLANK không có tương đương trực tiếp — SQL đếm bằng COUNT(*) FILTER (WHERE cot IS NULL).

Điều kiện có NULL

NULL không bằng bất kỳ giá trị nào, kể cả NULL. Điều này làm SUMIF kiểu Excel và SQL khác nhau:

-- Sai: không đếm được dòng có city NULL
SELECT COUNT(*) FILTER (WHERE city = 'Hà Nội') FROM customers;

-- Đếm dòng thiếu city
SELECT COUNT(*) FILTER (WHERE city IS NULL) FROM customers;

Khi tính tỷ lệ, mẫu số nên là số dòng có thể thỏa điều kiện, không phải tổng số dòng. Ví dụ tỷ lệ đơn hoàn thành = completed / (completed + pending + cancelled), loại các trạng thái không xác định.

Bảng KPI động theo tháng

Excel thường có cột tháng và các cột chỉ số. SQL nhóm theo tháng:

SELECT date_trunc('month', o.order_date)::date AS thang,
       COUNT(DISTINCT o.id) AS so_don,
       SUM(oi.quantity * oi.unit_price) AS doanh_thu,
       COUNT(DISTINCT o.customer_id) AS so_khach
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'completed'
GROUP BY 1
ORDER BY 1;

date_trunc gom theo tháng; ::date bỏ phần giờ để hiển thị gọn. Đây là mẫu báo cáo thay thế PivotTable theo tháng.

Thực hành

1. Đếm số khách ở Hà Nội bằng FILTER và bằng SUM(CASE ...).
2. Tính doanh thu mỗi thành phố và giá trị đơn trung bình đúng hạt.
3. Đếm đơn theo ba trạng thái trong một truy vấn.
4. So COUNT(*), COUNT(email), COUNT(DISTINCT city).
5. Lập bảng doanh thu theo tháng bằng date_trunc.

Lỗi thường gặp

Tóm tắt

• COUNTIF/SUMIF → FILTER hoặc SUM(CASE ...).
• Một truy vấn có thể tính nhiều chỉ số bằng nhiều FILTER.
• COUNT(*), COUNT(cot), COUNT(DISTINCT cot) trả ba kết quả khác nhau.
• NULL cần IS NULL, không dùng =.
• Luôn kiểm tra hạt trước khi chia để tính trung bình.