SQL Server - Gom nhóm dữ liệu

1TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM
TRUNG TÂM TIN HỌC
Gom nhóm dữ liệu
2TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM
TRUNG TÂM TIN HỌC
Các hàm nhóm dữ liệu (group functions) ?
Các hàm nhóm dữ liệu là phép toán trên một tập 
các dòng để trả về một kết quả tương ứng mỗi 
nhóm NHANVIEN
Tổng lương các 
nhân viên trong 
bảng 
NHANVIEN .
…
3TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM
TRUNG TÂM TIN HỌC
Các hàm nhóm dữ liệu
• AVG ([DISTINCT|ALL] bieu_thuc)
• COUNT ({*|[DISTINCT|ALL] bieu_thuc}) 
• MAX ([DISTINCT|ALL] bieu_thuc)
• MIN ([DISTINCT|ALL] bieu_thuc)
• SUM ([DISTINCT|ALL] bieu_thuc)
4TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM
TRUNG TÂM TIN HỌC
SELECT [cot,] ham_nhom_du_lieu(cot), ...
FROM bang
[WHERE dieu_kien]
[GROUP BY cot]
[ORDER BY cot]
Cú pháp các hàm nhóm dữ liệu
5TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM
TRUNG TÂM TIN HỌC
SELECT AVG(mucluong) as AVG_LLuong, 
 MAX(mucluong) as MAX_Luong,
 MIN(mucluong) as MIN_Luong, 
 SUM(mucluong) as SUM_Luong
FROM qlns.nhanvien
WHERE macongviec LIKE ‘%TT%'
Sử dụng các hàm AVG,SUM & MAX,MIN
AVG và SUM : dùng cho dữ liệu kiểu số.
MIN and MAX : Có thể dùng cho phần lớn các kiểu dữ liệu
SELECT MIN(ngayvaolam), MAX(ngayvaolam)
FROM qlns.nhanvien
6TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM
TRUNG TÂM TIN HỌC
SELECT COUNT(*)
FROM qlns.nhanvien
WHERE maphong = 50
Sử dụng hàm COUNT
COUNT(*) Trả về số dòng có trong bảng.
COUNT(bieu_thuc) trả về số dòng khác null của 
bieu_thuc.
SELECT COUNT(phucap)
FROM qlns.nhanvien
WHERE maphong = 50
7TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM
TRUNG TÂM TIN HỌC
SELECT COUNT(DISTINCT maphong)
FROM qlns.nhanvien;
Sử dụng từ khóa DISTINCT
• COUNT(DISTINCT bieu_thuc) Trả về số dòng 
khác nhau và khác NULL của bieu_thuc.
• Ví dụ : Cho biết có bao nhiêu phòng ban khác 
nhau trong bảng NHANVIEN .
8TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM
TRUNG TÂM TIN HỌC
SELECT AVG(phucap)
FROM qlns.nhanvien
Giá trị NULL và trong các hàm nhóm dữ liệu
Các hàm nhóm dữ liệu sẽ tự động bỏ qua các giá 
trị NULL trong cột khi thực hiện tính toán.
9TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM
TRUNG TÂM TIN HỌC
Tạo các phân nhóm dữ liệu
NHANVIEN
Tổng lương 
trong bảng
NHANVIEN
của mỗi 
phòng
15.000.000
…
16.500.000
13.000.000
5.200.000
10.500.000
8.500.000
4.000.000
10
TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM
TRUNG TÂM TIN HỌC
SELECT cot, ham_nhom_du_lieu(cot)
FROM bang
[WHERE dieu_kien]
[GROUP BY bieu_thuc_phan_nhom]
[ORDER BY cot]
Tạo các phân nhóm dữ liệu : 
Mệnh đề GROUP BY
11
TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM
TRUNG TÂM TIN HỌC
SELECT maphong, AVG(mucluong)
FROM qlns.nhanvien
GROUP BY maphong 
Sử dụng mệnh đề GROUP BY
Tất cả các cột trong danh sách của SELECT nếu không 
phải là hàm nhóm dữ liệu thì phải tồn tại trong mệnh 
đề GROUP BY .
12
TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM
TRUNG TÂM TIN HỌC
Sử dụng mệnh đề GROUP BY
Các cột trong GROUP BY không bắt buộc phải có trong 
danh sách của SELECT
SELECT AVG(mucluong)
FROM qlns.nhanvien
GROUP BY maphong 
13
TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM
TRUNG TÂM TIN HỌC
Tạo phân nhóm trên nhiều cột
NHANVIEN
“Tính tổng 
lương cho 
mỗi công 
việc và 
phân nhóm 
theo phòng 
ban
…
14
TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM
TRUNG TÂM TIN HỌC
SELECT maphong maph, macongviec, SUM(mucluong)
FROM qlns.nhanvien
GROUP BY maphong, macongviec
ORDER BY maphong, macongviec 
Sử dụng GROUP BY trên nhiều cột
15
TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM
TRUNG TÂM TIN HỌC
Một số lưu ý khi sử dụng các hàm 
nhóm dữ liệu
Bất kỳ một cột hay biểu thức trong danh sách của 
SELECT không phải là hàm nhóm dữ liệu thì phải 
có mệnh đề GROUP BY 
SELECT maphong, COUNT(tennv)
FROM qlns.nhanvien
Thiếu các cột trong mệnh đề GROUP BY
16
TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM
TRUNG TÂM TIN HỌC
Một số lưu ý khi sử dụng các hàm 
nhóm dữ liệu
• Không thể sử dụng mệnh đề WHERE để giới hạn bớt 
các nhóm.
• Sử dụng mệnh đề HAVING để lọc bớt các nhóm.
• Không được phép sử dụng các hàm nhóm dữ liệu 
trong mệnh đề WHERE.
SELECT maphong, AVG(mucluong)
FROM qlns.nhanvien
WHERE AVG(mucluong) > 8000000
GROUP BY maphong
Không thể sử dụng WHERE để lọc bớt các nhóm
17
TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM
TRUNG TÂM TIN HỌC
Lọc bớt kết quả phân nhóm
Liệt kê 
những phòng 
ban có MAX 
lương lớn 
hon 
8.500.000 đ
NHANVIEN
…
18
TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM
TRUNG TÂM TIN HỌC
SELECT cotcolumn, ham_nho_du_lieu
FROM bang
[WHERE dieu_kien]
[GROUP BY bieu_thuc_nhom_du_lieu]
[HAVING dieu_kien_loc_nhom_du_lieu]
[ORDER BY cot]
Mệnh đề HAVING
Để giới hạn bớt các nhóm trả về :
1. Các dòng đã được phân nhóm.
2. Các hàm nhóm dữ liệu đã đưa vào.
3. Chỉ những nhóm thỏa điều kiện HAVING mới 
nằm trong tập kết quả trả về.
19
TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM
TRUNG TÂM TIN HỌC
Sử dụng mệnh đề HAVING
SELECT maphong, MAX(mucluong) as [Max Luong]
FROM qlns.nhanvien
GROUP BY maphong
HAVING MAX(mucluong)>=10.000.000 
20
TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM
TRUNG TÂM TIN HỌC
SELECT macongviec, SUM(mucluong) TIENLUONG
FROM qlns.nhanvien
WHERE macongviec NOT LIKE '%TTHI%'
GROUP BY macongviec
HAVING SUM(mucluong) >= 10.000.000
ORDER BY SUM(mucluong)
Sử dụng mệnh đề HAVING
21
TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM
TRUNG TÂM TIN HỌC
Thực hành
• Viết truy vấn sử dụng các hàm phân nhóm
• Phân nhóm các dòng kết hợp hàm phân nhóm
• Kết hợp mệnh đề HAVING, mệnh đề WHERE