Truy vấn MySQL tổng hợp 3 giá trị khác nhau trong một cột, hiển thị tổng từng giá trị

Sử dụng CASE bên trong SUM() kết hợp GROUP BY. Tạo bảng:

mysql> CREATE TABLE DemoTable (
    ProductName VARCHAR(100),
    ProductRating ENUM('1','2','3')
); Query OK, 0 rows affected (0.50 sec)

Chèn dữ liệu:

mysql> INSERT INTO DemoTable VALUES('Product-1',3); Query OK, 1 row affected (0.12 sec)
mysql> INSERT INTO DemoTable VALUES('Product-2',1); Query OK, 1 row affected (0.08 sec)
mysql> INSERT INTO DemoTable VALUES('Product-3',2); Query OK, 1 row affected (0.12 sec)
mysql> INSERT INTO DemoTable VALUES('Product-1',2); Query OK, 1 row affected (0.10 sec)
mysql> INSERT INTO DemoTable VALUES('Product-3',3); Query OK, 1 row affected (0.13 sec)
mysql> INSERT INTO DemoTable VALUES('Product-2',2); Query OK, 1 row affected (0.13 sec)
mysql> INSERT INTO DemoTable VALUES('Product-3',3); Query OK, 1 row affected (0.10 sec)

Hiển thị dữ liệu:

mysql> SELECT * FROM DemoTable;

Kết quả:

+-------------+---------------+
| ProductName | ProductRating |
+-------------+---------------+
| Product-1   | 3             |
| Product-2   | 1             |
| Product-3   | 2             |
| Product-1   | 2             |
| Product-3   | 3             |
| Product-2   | 2             |
| Product-3   | 3             |
+-------------+---------------+
7 rows in set (0.00 sec)

Truy vấn đếm số lượng từng rating theo sản phẩm:

mysql> SELECT 
    ProductName,
    SUM(CASE WHEN ProductRating=3 THEN 1 ELSE 0 END) AS Rating_3,
    SUM(CASE WHEN ProductRating=2 THEN 1 ELSE 0 END) AS Rating_2,
    SUM(CASE WHEN ProductRating=1 THEN 1 ELSE 0 END) AS Rating_1
FROM DemoTable 
GROUP BY ProductName;

Kết quả:

+-------------+----------+----------+----------+
| ProductName | Rating_3 | Rating_2 | Rating_1 |
+-------------+----------+----------+----------+
| Product-1   | 1        | 1        | 0        |
| Product-2   | 0        | 1        | 1        |
| Product-3   | 2        | 1        | 0        |
+-------------+----------+----------+----------+
3 rows in set (0.00 sec)