Bài 4.3 Gom nhóm dữ liệu SQL (STRUCTURED QUERY LANGUAGE)


Tìm hiểu và cách gom nhóm dữ liệu: Avg, Sum, Max, Min, Count, Distinct, Group By, Having


Gom nhóm dữ liệu

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

Gom nhóm dữ liệu

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)

Cú pháp các hàm nhóm dữ liệu

SELECT [cot,] ham_nhom_du_lieu(cot), ...
FROM bang
[WHERE dieu_kien]
[GROUP BY cot]
[ORDER BY cot]

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 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%'

demo avg

SELECT MIN(ngayvaolam), MAX(ngayvaolam)
FROM qlns.nhanvien

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(*)
FROM qlns.nhanvien
WHERE maphong = 50

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
ELECT COUNT(DISTINCT maphong)
FROM qlns.nhanvien;

Tạo các phân nhóm dữ liệu

Tạo các phân nhóm dữ liệu

Mệnh đề GROUP BY

SELECT cot, ham_nhom_du_lieu(cot)
FROM bang
[WHERE dieu_kien]
[GROUP BY bieu_thuc_phan_nhom]
[ORDER BY cot]

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
SELECT maphong, AVG(mucluong)
FROM qlns.nhanvien
GROUP BY maphong

group by demo 1

  • 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

group by demo 2

Tạo phân nhóm trên nhiều cột

Tạo phân nhóm trên nhiều cột

SELECT maphong maph, macongviec, SUM(mucluong)
FROM qlns.nhanvien
GROUP BY maphong, macongviec
ORDER BY maphong, macongviec

sử dụng gom nhóm nhiều dòng

Lưu ý 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
  • 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.

Lọc bớt kết quả phân nhóm

Lọc bớt kết quả phân nhóm

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ề.
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]

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

Bài tập

  1. Hiển thị danh sách các nhà cung cấp gồm các cột: mã nhà cung cấp, tên nhà cung cấp và tổng số đơn đặt hàng. Lọc ra các nhà cung cấp nào có tổng số đơn đặt hàng lớn hơn 1
  2. Hiển thị danh sách các thông tin trong bảng PNHAP có thêm cột tổng thành tiền, biết rằng Tổng thành tiền = Σ(sốlượng nhập x đơn giá nhập) của các mẩu tin chi tiết tương ứng trong bảng CTPNHAP
  3. Hiển thị danh sách các phiếu xuất hàng gồm các cột: sốphiếu xuất và tổng thành tiền. Trong đó sắp xếp theo thứ tự tổng thành tiền giảm dần
  4. Hiển thị danh sách các phiếu xuất hàng có tổng thành tiền lớn nhất
--Câu 1
SELECT cc.manhacc,cc.tennhacc,COUNT(dh.sodh) AS 'Tổng đơn đặt hàng'
FROM nhacc AS cc
        LEFT JOIN dondh AS dh ON cc.manhacc = dh.manhacc
GROUP BY cc.manhacc,cc.tennhacc
HAVING COUNT(dh.sodh) > 1
 
--Câu 2
SELECT pn.sopn,pn.sodh,pn.ngaynhap,SUM(cn.dgnhap*cn.slnhap) AS 'Tổng tiền nhập'
FROM pnhap AS pn
        LEFT JOIN ctpnhap AS cn ON pn.sopn = cn.sopn
GROUP BY pn.sopn,pn.sodh,pn.ngaynhap
 
--Câu 3
SELECT cx.sopx,SUM(cx.dgxuat*cx.slxuat) AS 'Tổng tiền xuất'
FROM pxuat AS px
        LEFT JOIN ctpxuat AS cx ON px.sopx = cx.sopx
GROUP BY cx.sopx
ORDER BY SUM(cx.dgxuat*cx.slxuat) DESC
 
--Câu 4
SELECT cx.sopx,MAX(cx.dgxuat*cx.slxuat) AS 'Tổng thành tiền lớn nhất'
FROM pxuat AS px
        LEFT JOIN ctpxuat AS cx ON px.sopx = cx.sopx
GROUP BY cx.sopx

Chia sẽ bài viết :


Bài viết liên quan