GROUP BY หลายคอลัมน์ใน MySQL ด้วย IF() และ Conditional Aggregation

แนวคิด

ใช้ IF() หรือ CASE WHEN ภายใน aggregate function เพื่อ GROUP BY หลายคอลัมน์พร้อมเงื่อนไข

รูปแบบคำสั่ง (ตัวอย่างเดิม)

SELECT CustomerId, IF(ProductName = 'Product-2', 1, 0) 
FROM MultipleGroupByDemo 
GROUP BY ProductName, CustomerId;

ตัวอย่างครบถ้วน

1. สร้างตารางสั่งซื้อ

mysql> create table MultipleGroupByDemo 
    -> ( 
    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    -> CustomerId int, 
    -> ProductName varchar(100) 
    -> ); 
Query OK, 0 rows affected (0.59 sec)

2. เพิ่มข้อมูล

mysql> insert into MultipleGroupByDemo(CustomerId,ProductName) values(1000,'Product-1'); 
Query OK, 1 row affected (0.20 sec)
mysql> insert into MultipleGroupByDemo(CustomerId,ProductName) values(1001,'Product-2'); 
Query OK, 1 row affected (0.18 sec)
mysql> insert into MultipleGroupByDemo(CustomerId,ProductName) values(1001,'Product-2'); 
Query OK, 1 row affected (0.16 sec)
mysql> insert into MultipleGroupByDemo(CustomerId,ProductName) values(1001,'Product-2'); 
Query OK, 1 row affected (0.12 sec)
mysql> insert into MultipleGroupByDemo(CustomerId,ProductName) values(1002,'Product-3'); 
Query OK, 1 row affected (0.09 sec)
mysql> insert into MultipleGroupByDemo(CustomerId,ProductName) values(1002,'Product-3'); 
Query OK, 1 row affected (0.15 sec)
mysql> insert into MultipleGroupByDemo(CustomerId,ProductName) values(1003,'Product-4'); 
Query OK, 1 row affected (0.16 sec)

3. ดูข้อมูล

mysql> select * from MultipleGroupByDemo;

+----+------------+-------------+
| Id | CustomerId | ProductName |
+----+------------+-------------+
| 1  | 1000       | Product-1   |
| 2  | 1001       | Product-2   |
| 3  | 1001       | Product-2   |
| 4  | 1001       | Product-2   |
| 5  | 1002       | Product-3   |
| 6  | 1002       | Product-3   |
| 7  | 1003       | Product-4   |
+----+------------+-------------+

4. GROUP BY ProductName, CustomerId พร้อม IF

mysql> SELECT CustomerId, IF(ProductName = 'Product-2', 1, 0) 
    -> FROM MultipleGroupByDemo 
    -> GROUP BY ProductName, CustomerId;

+------------+---------------------------------------+
| CustomerId | IF(ProductName = 'Product-2', 1, 0)   |
+------------+---------------------------------------+
| 1000       | 0                                     |
| 1001       | 1                                     |
| 1002       | 0                                     |
| 1003       | 0                                     |

5. Alternate: Conditional Aggregation (แนะนำ)

mysql> SELECT CustomerId, 
    -> MAX(IF(ProductName = 'Product-2', 1, 0)) AS has_product2 
    -> FROM MultipleGroupByDemo 
    -> GROUP BY CustomerId;

+------------+--------------+
| CustomerId | has_product2 |
+------------+--------------+
| 1000       | 0            |
| 1001       | 1            |
| 1002       | 0            |
| 1003       | 0            |
+------------+--------------+

Conditional Aggregation Patterns

Pivot Table (Crosstab) ตัวอย่าง

SELECT 
  CustomerId,
  SUM(CASE WHEN ProductName = 'Product-1' THEN 1 ELSE 0 END) AS `Product-1`,
  SUM(CASE WHEN ProductName = 'Product-2' THEN 1 ELSE 0 END) AS `Product-2`,
  SUM(CASE WHEN ProductName = 'Product-3' THEN 1 ELSE 0 END) AS `Product-3`,
  SUM(CASE WHEN ProductName = 'Product-4' THEN 1 ELSE 0 END) AS `Product-4`
FROM MultipleGroupByDemo
GROUP BY CustomerId;

+------------+-----------+-----------+-----------+-----------+
| CustomerId | Product-1 | Product-2 | Product-3 | Product-4 |
+------------+-----------+-----------+-----------+-----------+
| 1000       | 1         | 0         | 0         | 0         |
| 1001       | 0         | 3         | 0         | 0         |
| 1002       | 0         | 0         | 2         | 0         |
| 1003       | 0         | 0         | 0         | 1         |
+------------+-----------+-----------+-----------+-----------+

MySQL 8.0: ใช้ FILTER clause (PostgreSQL style - ไม่รองรับโดยตรง)

MySQL ยังไม่รองรับ SUM(...) FILTER (WHERE condition) ต้องใช้ CASE WHEN

สรุป: Conditional Aggregation (MAX/SUM + CASE) = วิธีมาตรฐาน GROUP BY หลายเงื่อนไขใน MySQL