MySQL GROUP BY được sử dụng như thế nào ?

Trong bài viết này, bạn sẽ học cách sử dụng MySQL GROUP BY để nhóm các hàng thành các nhóm con dựa trên giá trị của các cột hoặc biểu thức.

Giới thiệu về mệnh đề MySQL GROUP BY

Mệnh đề GROUP BY sẽ nhóm một tập hợp các hàng thành một tập hợp các hàng tóm tắt theo giá trị của các cột hoặc biểu thức. Mệnh đệ GROUP BY trả về một hàng cho mỗi nhóm. Nói cách khác, nó làm giảm số lượng hàng trong tập hợp kết quả.

Mệnh đề GROUP BY là mệnh đề tùy chọn của SELECT câu lệnh. Sau đây là cú pháp của mệnh đề GROUP BY:

SELECT 
    c1, c2,..., cn, aggregate_function(ci)
FROM
    table
WHERE
    where_conditions
GROUP BY c1 , c2,...,cn;

Trong cú pháp này, bạn đặt mệnh đề GROUP BY sau mệnh đề FROM và WHERE. Sau các từ khóa GROUP BY, bạn đặt danh sách các cột hoặc biểu thức được phân tách bằng dấu phẩy để nhóm các hàng.

MySQL đánh giá mệnh đề GROUP BY sau mệnh đề FROM và WHERE và trước mệnh đề HAVING, SELECT, DISTINCT, ORDER BY và LIMIT:

Thực tế thì bạn thường sử dụng GROUP BY với  aggregate functions như SUM, AVG, MAX, MINvà COUNT. Hàm tổng hợp xuất hiện trong mệnh đề SELECT cung cấp thông tin của từng nhóm.

Ví dụ về MySQL GROUP BY

Cùng lấy một số ví dụ về việc sử dụng mệnh đề GROUP BY.

A) Ví dụ đơn giản về MySQL GROUP BY

Hãy xem bảng orders trong cơ sở dữ liệu mẫu .

Giả sử bạn muốn nhóm các giá trị của trạng thái đơn hàng thành các nhóm con, bạn sử dụng mệnh đề GROUP BY với cột status như truy vấn sau:

SELECT 
    status
FROM
    orders
GROUP BY status;

Kết quả:

Như bạn thấy từ tập kết quả, mệnh đề GROUP BY đề trả về status các giá trị xuất hiện duy nhất. Nó hoạt động giống như toán tử DISTINCT được hiển thị trong truy vấn sau:

SELECT DISTINCT
    status
FROM
    orders;

B) Sử dụng MySQL GROUP BY với các hàm tổng hợp

Các hàm tổng hợp cho phép bạn thực hiện tính toán một tập hợ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ổng hợp để thực hiện các phép tính và trả về một giá trị duy nhất cho mỗi nhóm con.

Ví dụ bạn muốn biết số lượng đơn hàng trong từng trạng thái thì có thể sử dụng hàm COUNT với mệnh đề GROUP BY như sau:

SELECT 
    status, COUNT(*)
FROM
    orders
GROUP BY status;

Kết quả:

Xem phần sau bảng orders và bảng  orderdetails.

Để lấy tổng tiền của tất cả các đơn hàng theo trạng thái, bạn JION bảng orders với bảng orderdetails và dùng hàm tính tổng tiền SUM. Xem truy vấn sau:

SELECT 
    status, 
    SUM(quantityOrdered * priceEach) AS amount
FROM
    orders
INNER JOIN orderdetails 
    USING (orderNumber)
GROUP BY 
    status;

Kết quả:

Tương tự, truy vấn sau đây trả về số đơn đặt hàng và tổng số tiền của mỗi đơn đặt hàng.

SELECT 
    orderNumber,
    SUM(quantityOrdered * priceEach) AS total
FROM
    orderdetails
GROUP BY 
    orderNumber;

Kết quả:

C) MySQL GROUP BY với ví dụ về biểu thức

Ngoài các cột, bạn có thể nhóm các hàng theo biểu thức. Truy vấn sau đây nhận được tổng doanh thu cho mỗi năm.

SELECT 
    YEAR(orderDate) AS year,
    SUM(quantityOrdered * priceEach) AS total
FROM
    orders
INNER JOIN orderdetails 
    USING (orderNumber)
WHERE
    status = 'Shipped'
GROUP BY 
    YEAR(orderDate);

kết quả:

Trong ví dụ này, chúng tôi đã sử dụng hàm YEAR để trích xuất dữ liệu năm từ ngày đặt hàng ( orderDate). Chúng tôi chỉ bao gồm các đơn đặt hàng có trạng thái shipped trong tổng doanh số. Lưu ý rằng biểu thức xuất hiện trong mệnh đề SELECT phải giống với biểu thức trong mệnh đề GROUP BY.

D) Sử dụng MySQL GROUP BY với ví dụ mệnh đề HAVING

Để lọc các nhóm được trả về bởi mệnh đề GROUP BY, bạn sử dụng một mệnh đề  HAVING. Truy vấn sau sử dụng mệnh đề HAVING để chọn tổng doanh số của các năm sau năm 2003.

SELECT 
    YEAR(orderDate) AS year,
    SUM(quantityOrdered * priceEach) AS total
FROM
    orders
INNER JOIN orderdetails 
    USING (orderNumber)
WHERE
    status = 'Shipped'
GROUP BY 
    year
HAVING 
    year > 2003;

kết quả:

Mệnh đề GROUP BY: Tiêu chuẩn MySQL so với SQL

Tiêu chuẩn SQL không cho phép bạn sử dụng bí danh (alias) trong mệnh đề GROUP BY trong khi MySQL hỗ trợ điều này.

Ví dụ: truy vấn sau trích xuất năm từ ngày đặt hàng. Trước tiên, nó sử dụng bí danh year của biểu thức YEAR(orderDate)và sau đó sử dụng bí danh year trong mệnh đề GROUP BY.

Truy vấn sau đây không hợp lệ trong tiêu chuẩn SQL:

SELECT 
    YEAR(orderDate) AS year, 
    COUNT(orderNumber)
FROM
    orders
GROUP BY 
    year;

kết quả:

Ngoài ra, MySQL cho phép bạn sắp xếp các nhóm theo thứ tự tăng dần hoặc giảm dần. Thứ tự sắp xếp mặc định là tăng dần. Ví dụ: nếu bạn muốn lấy số lượng đơn đặt hàng theo trạng thái và sắp xếp trạng thái theo thứ tự giảm dần, bạn có thể sử dụng mệnh đề GROUP BY with DESC làm truy vấn sau:

SELECT 
    status, 
    COUNT(*)
FROM
    orders
GROUP BY 
    status DESC;

kết quả:

Lưu ý DESC trong mệnh đề GROUP BY sắp xếp status theo thứ tự giảm dần. Và bạn cũng có thể sử dụng ASC  rõ ràng trong mệnh đề GROUP BY để sắp xếp các nhóm theo trạng thái theo thứ tự tăng dần.

Mệnh đề GROUP BY so với mệnh đề DISTINCT

Nếu bạn sử dụng mệnh đề GROUP BY trong câu lệnh SELECT mà không sử dụng các hàm tổng hợp , thì mệnh đề GROUP BY đó sẽ hoạt động giống như DISTINCT mệnh đề.

Câu lệnh sau sử dụng mệnh đề GROUP BY để chọn các trạng thái duy nhất của khách hàng từ bảng customers.

SELECT 
    state
FROM
    customers
GROUP BY state;

kết quả:

Bạn có thể đạt được kết quả tương tự bằng cách sử dụng mệnh đề DISTINCT:

Nói chung, mệnh đề DISTINCT là trường hợp đặc biệt của mệnh đề GROUP BY. Sự khác biệt giữa mệnh đề DISTINCT và mệnh đề GROUP BY là mệnh đề GROUP BY sắp xếp tập kết quả , trong khi mệnh đề DISTINCT thì không.

Lưu ý rằng MySQL 8.0 đã loại bỏ cách sắp xếp ngầm cho mệnh đề GROUP BY. Do đó, nếu bạn sử dụng MySQL 8.0+, bạn sẽ thấy tập kết quả của truy vấn trên với mệnh đề GROUP BY không được sắp xếp.

Nếu bạn thêm mệnh đề ORDER BY vào câu lệnh sử dụng mệnh đề  DISTINCT đó, tập kết quả sẽ được sắp xếp và nó giống với kết quả được trả về bởi câu lệnh sử dụng mệnh đề GROUP BY.

SELECT DISTINCT
    state
FROM
    customers
ORDER BY 
    state;

Tổng kết

• Sử dụng mệnh đề GROUP BY để nhóm các hàng thành các nhóm con.

Các bài viết liên quan:

• Mysql JOIN
• Mysql inner join
• Mysql left join