- [VB.NET] Chia sẻ source code lịch âm dương và hẹn lịch nhắc việc
- [C#] Hướng dẫn đọc thông số thiết bị Thiết bị kiểm tra Pin (HIOKI BATTERY HiTESTER BT3562)
- [VB.NET] Hướng dẫn giải captcha sử dụng dịch vụ AZCaptcha API trên winform
- [C#] Hướng dẫn chứng thực đăng nhập ứng dụng bằng vân tay (Finger Print) trên máy tính
- [C#] Color Thief cách xuất màu sắc thiết kế từ hình ảnh
- [C#] Cách tạo bản quyền và cho phép dùng thử ứng dụng Winform
- [C#] Hướng dẫn sử dụng trình duyệt web Chrome convert HTML sang tập tin file PDF
- [C#] Kết nôi điện thoại Android, IOS với App Winform via Bluetooth
- [DATABASE] Cách query cộng trừ dồn dần trong Sqlserver
- [C#] Thiết kế ứng dụng Console đẹp với thư viện Spectre.Console
- [C#] Thiết kế ứng dụng Single Instance và đưa ứng dụng lên trước nếu kiểm tra ứng dụng đang chạy
- [C#] Giới thiệu JSON Web Token và cách đọc chuỗi token
- [C#] Cách tăng giảm font chữ tất cả các control trên winform
- [DEVEXPRESS] Tích hợp chức năng Tìm kiếm Search vào CheckedComboboxEdit
- [C#] Gởi email Metting Calendar Reminder kèm nhắc thời gian lịch họp
- [C#] Tìm kiếm xem danh sách từ khóa có tồn tại trong đoạn văn bản hay không
- [C#] Thiết kế giao diện ứng dụng trên Console sử dụng thư viện Terminal.Gui
- [C#] Hướng dẫn tạo mã VietQR Payment API Winform
- [C#] Sử dụng thư viện BenchmarkDotNet đo hiệu năng của hảm Method
- [DEVEXPRESS] Tìm kiếm không dấu tô màu highlight có dấu trên C# Winform
[SQLSERVER] Sử dụng GROUP BY và ROLLUP, CUBE để thống kê dữ liệu
Bài viết này được viết bởi Tác Giả: Phạm Ngọc Hùng, mình thấy bài viết này rất hay và hữu ích nên đăng lại để các bạn có thể tham khảo.
[SQLSERVER] Sử dụng GROUP BY và ROLLUP, CUBE để thống kê dữ liệu
Để thống kê dữ liệu, các hệ quản trị cơ sở dữ liệu cung cấp các hàm tổng hợp dữ liệu như AVG(), SUM(), COUNT(),… sử dụng kết hợp với mệnh đề GROUP BY.
Bài viết này giới thiệu cách sử dụng mệnh đề GROUP BY và các tùy chọn mở rộng ROLLUP, CUBE với các hàm tổng hợp dữ liệu trong hệ quản trị CSDL SQL Server.
Tạo dữ liệu minh họa
Trước hết, ta cần tạo dữ liệu để minh họa cho ví dụ này. Xét một TABLE Student chứa thông tin về điểm các môn học của sinh viên:
Tạo bảng trên bằng lệnh CREATE TABLE sau:
create table Student ( [name] nchar(30), [subject] nchar(10), [mark] float);
Chèn dữ liệu vào trong TABLE Student như sau:
insert into Student values('Hung', 'JAVA', 8);
insert into Student values('Hung', 'C', 9);
insert into Student values('Hung', 'C', 7);
insert into Student values('Hung', 'SQL', 5);
insert into Student values('Tuan', 'JAVA', 4);
insert into Student values('Tuan', 'C', 7);
insert into Student values('Tuan', 'SQL', 10);
insert into Student values('Tuan', 'SQL', 5);
Để kiểm tra dữ liệu đã được thêm vào đủ chưa, ta chạy lệnh sau:
SELECT [name] AS [Student name], [subject] AS [Subject name], [mark] AS [Mark] FROM Student;
Khi đó, ta thu được kết quả giống như hình minh họa ở trên.
Thống kê dữ liệu
Sử dụng mệnh đề GROUP BY cùng với hàm tính giá trị trung bình AVG, ta có thể tính điểm trung bình theo môn của từng sinh viên. Gõ lệnh SELECT (1) như sau:
SELECT [name] as [Student name], [subject] as [Subject name],
AVG([mark]) as [Average mark] FROM Student GROUP BY [name], [subject];
Lệnh này sẽ trả lại kết quả như sau:
Cột bên thứ 3 (không có tên) chứa điểm trung bình từng môn của sinh viên.
Trong việc quản lý sinh viên, người dùng còn có nhu cầu tính điểm trung bình tất cả các môn của mỗi sinh viên và điểm trung bình chung của tất cả sinh viên như sau:
Khi đó, ta sử dụng thêm các tùy chọn ROLLUP và CUBE vào trong mệnh đề GROUP BY.
Thay lệnh SELECT (1) ở trên bằng lệnh SELECT (2) có thêm tùy chọn ROLLUP như sau:
SELECT [name] as [Student name], [subject] as [Subject name], AVG([mark]) as [Average mark]
FROM Student GROUP BY [name], [subject] WITH ROLLUP;
Ta thu được kết quả:
Trong kết quả trên, tùy chọn ROLLUP đã chèn thêm các dòng tính điểm trung bình tất cả môn học của từng sinh viên. Tại ô tên môn học, ROLLUP cho giá trị NULL.
Ta có thể sửa các giá trị NULL này thành tên gọi mang ý nghĩa tại các ô đó bằng cách sử dụng Hàm GROUPING()
để xác định xem trường (FIELD) [name] hoặc [subject] có phải hiện đang được nhóm hay không với lệnh SELECT (3) như sau:
SELECT
CASE when GROUPING([name]) = 1 THEN 'All students' ELSE [name] END
AS [Student name],
CASE when GROUPING([subject]) = 1 THEN 'All subjects' ELSE [subject] END
AS [Subject name],
CAST(AVG([mark]) AS DECIMAL(9, 2)) AS [Average mark]
FROM Student GROUP BY [name], [subject] WITH ROLLUP;
Khi đó, ta sẽ nhận được kết quả như mong muốn:
Ta có nhận xét là, trong kết quả trên, ta chỉ tính được điểm trung bình từng môn của từng sinh viên cũng như điểm trung bình tất cả các môn của từng sinh viên, (tức là tính trung bình theo sinh viên), nhưng không có điểm trung bình của tất cả sinh viên cho từng môn.(chẳng hạn điểm trung bình của tất cả sinh viên trong môn lập trình C).
Để tính loại điểm trung bình thứ 3, ta có thể đảo ngược vị trí của [name] và [subject] trong mệnh đề GROUP BY như sau:
SELECT [subject] as [Subject name], [name] as [Student name], AVG([mark])
FROM Student GROUP BY [subject], [name] WITH ROLLUP;
Tuy nhiên, để kết quả có cả 3 loại điểm trung bình trên, tùy chọn ROLLUP không đáp ứng được, mà ta cần sử dụng tùy chọn CUBE.
Thay thế tùy chọn CUBE cho ROLLUP trong lệnh SELECT (3), ta có lệnh SELECT (4):
SELECT
CASE when GROUPING([name]) = 1 THEN 'All students' ELSE [name] END
AS [Student name],
CASE when GROUPING([subject]) = 1 THEN 'All subjects' ELSE [subject] END
AS [Subject name],
CAST(AVG([mark]) AS DECIMAL(9, 2)) AS [Average mark]
FROM Student GROUP BY [name], [subject] WITH CUBE;
Khi chạy lệnh này, ta thu được kết quả:
Have Fun :)