इसे समझने के लिए, 'Students' टेबल से निम्नलिखित डेटा पर विचार करें:
mysql> SELECT * FROM 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 |
+----+-----------+-----------+----------+----------------+
9 rows in set (0.00 sec)
अब, मान लीजिए यदि हम जानना चाहते हैं कि कितने छात्र USA, UK, NZ, INDIA, RUSSIA, FRANCE आदि से संबंधित हैं तो हम CASE स्टेटमेंट के भीतर 'country' कॉलम का उपयोग निम्नानुसार कर सकते हैं:
mysql> 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 |
+------+------+-------+--------+--------+------+-----------+
1 row in set (0.07 sec)
व्याख्या: CASE WHEN कॉलम=मान THEN 1 ELSE 0 END प्रत्येक पंक्ति के लिए 1/0 देता है, SUM कुल गिनता है। यह पिवट/क्रॉस-टैब क्वेरी का सामान्य पैटर्न है। MySQL 8.0+ में COUNT(CASE WHEN ... THEN 1 END) भी काम करता है।
