[Thực hành] Câu lệnh GROUP BY

[Thực hành] Câu lệnh GROUP BY

Mục đích

Luyện tập sử dụng câu lệnh GROUP BY.

Mô tả bài toán

Sử dụng csdl classicmodels để thực hiện các truy vấn với các hàm tập hợp kết hợp với mệnh đề group by, having.

Hướng dẫn nộp bài:

Up code lên github

Paste link github vào phần nộp bài

Huớng dẫn

Bước 1: Sử dụng mệnh đề group by

Quan sát bảng orders trong csdl classicmodels.

Để biết được có bao nhiêu trạng thái của đơn hàng. Thực hiện như thế nào?

Sử dụng câu lệnh select status from orders thì sẽ trả về toàn bộ số bản ghi trong bảng đơn hàng, kể cả những bản ghi có trạng thái trùng nhau, tức là kết quả trả về như hình dưới, chúng ta sẽ không thể thực hiện việc đếm hay tự lọc bằng mắt để lấy về các trạng thái của đơn hàng vì số lượng bản ghi trả về rất nhiều.

Để giải quyết vấn đề trên chúng ta sử dụng mệnh đề group by với cột status như sau:

select status from orders group by status

Kết quả trả về hiển thị các trạng thái khác nhau của đơn hàng.

Vậy nhận thấy mệnh đề group by là một phần không bắt buộc của câu lệnh select, được sử dụng để nhóm các tập các hàng có cùng một giá trị vào thành một nhóm, mỗi nhóm đó chỉ trả về một hàng.

Bước 2: Sử dụng các hàm tập hợp với mệnh đề group by

Các hàm tập hợp (SUM, MAX, MIN, COUNT, AVG) cho phép tính toán một tập các hàng và trả về một giá trị duy nhất. Mệnh đề group by thường được sử dụng với một hàm tập để thực hiện việc tính toán và trả về một giá trị cho mỗi nhóm.

Bài toán đặt ra như sau muốn biết có bao nhiêu đơn đặt hàng trong mỗi trạng thái, chúng ta có thể sử dụng hàm COUNT với mệnh đề GROUP BY như sau:

select status, COUNT(*) as 'So luong status'from ordersgroup by status

Kết quả trả về:

Bước 3: Sử dụng group by với hàm tập hợp truy vấn trên hai bảng.

Quan sát bảng orders và orderdetails.

Hãy thực hiện tính tổng số tiền của các đơn hàng theo trạng thái đặt hàng. Ta sẽ sử dụng lệnh inner join để kết nối 2 bảng và sử dụng hàm sum để tính tổng số tiền, nhóm theo trường status. Lệnh truy vấn được như sau:

select status, sum(quantityOrdered * priceEach) as amountfrom orders 
inner join orderdetails on orders.ordernumber = orderdetails.ordernumbergroup by status

Kết quả trả về:

Bước 4: Một yêu cầu khác là tính tổng tiền của từng đơn hàng, thực hiện lệnh truy vấn sau:

select orderNumber, sum(quantityOrdered * priceEach) as totalfrom orderdetailsgroup by orderNumber;

Kết quả trả về:

Bước 5: GROUP BY với mệnh đề HAVING

Để lọc các nhóm được trả về bởi mệnh đề GROUP BY, chúng ta sử dụng mệnh đề HAVING. Truy vấn sau đây sử dụng mệnh đề HAVING để bao gồm tổng doanh thu của những năm lớn hơn năm 2003.

select year(orderDate) as year, sum(quantityOrdered * priceEach) as totalfrom orders 
inner join orderdetails on orders.orderNumber = orderdetails.orderNumberwhere status = 'shipped'group by yearhaving year > 2003

Vậy mệnh đề having thay cho where để hạn chế dữ liệu trả về trong tập kết quả. Mệnh đề where dùng với các cột của bảng, tuy nhiên không dùng được với các hàm tập hợp. Mệnh đề having dùng với các hàm tập hợp.

Lưu ý thứ tự thực hiện các lệnh.

select columnname(s) from tablename where conditionalgroup by ... having ...

Tổng kết

Qua bài học này bạn đã luyện tập:

• Cách truy vấn dữ liệu với mệnh đề group by
• Sử dụng các hàm tập hợp
• Sử dụng mệnh đề having
• So sánh giữa having và where