CASE กับคอลัมน์: Conditional Aggregation
นับ/รวมเงื่อนไขต่อกลุ่ม (Group by implied)
ข้อมูล Students
+----+-----------+-----------+----------+----------------+
| id | Name | Country | Language | Course |
+----+-----------+-----------+----------+----------------+
| 1 | Francis | UK | English | Literature |
| 2 | Rick | USA | English | History |
| 3 | Correy | USA | English | Computers |
| 4 | Shane | France | French | Computers |
| 5 | Validimir | Russia | Russian | Computers |
| 6 | Steve | Australia | English | Geoinformatics |
| 7 | Rahul | India | Hindi | Yoga |
| 8 | Harshit | India | Hindi | Computers |
| 9 | Harry | NZ | English | Electronics |
+----+-----------+-----------+----------+----------------+
คำสั่ง: นับจำนวนนักเรียนต่อประเทศ
SELECT
SUM(CASE WHEN country = 'USA' THEN 1 ELSE 0 END) AS USA,
SUM(CASE WHEN country = 'UK' THEN 1 ELSE 0 END) AS UK,
SUM(CASE WHEN country = 'INDIA' THEN 1 ELSE 0 END) AS INDIA,
SUM(CASE WHEN country = 'Russia' THEN 1 ELSE 0 END) AS Russia,
SUM(CASE WHEN country = 'France' THEN 1 ELSE 0 END) AS France,
SUM(CASE WHEN country = 'NZ' THEN 1 ELSE 0 END) AS NZ,
SUM(CASE WHEN country = 'Australia' THEN 1 ELSE 0 END) AS Australia
FROM Students;
ผลลัพธ์
+------+------+-------+--------+--------+------+-----------+
| USA | UK | INDIA | Russia | France | NZ | Australia |
+------+------+-------+--------+--------+------+-----------+
| 2 | 1 | 2 | 1 | 1 | 1 | 1 |
+------+------+-------+--------+--------+------+-----------+
เทคนิค: Pivot Table (แปลงแถวเป็นคอลัมน์)
SELECT
Course,
SUM(CASE WHEN Language = 'English' THEN 1 ELSE 0 END) AS English,
SUM(CASE WHEN Language = 'Hindi' THEN 1 ELSE 0 END) AS Hindi,
SUM(CASE WHEN Language = 'French' THEN 1 ELSE 0 END) AS French
FROM Students
GROUP BY Course;
ประโยชน์
- Single-pass aggregation (อ่านตารางครั้งเดียว)
- หลีกเลี่ยง Multiple queries / UNION
- สร้าง Report / Dashboard ได้ง่าย
