How to use column data in MySQL CASE statement?-Mysql Tutorial-php.cn

How to use column data in MySQL CASE statement?

PHPz

Release： 2023-09-11 16:05:13

forward

860 people have browsed it

如何在 MySQL CASE 语句中使用列数据？

To understand it, consider the data in the "Students" table as shown below -

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)

Copy after login

Now, suppose we want to know how many students belong to United States, United Kingdom, New Zealand, India, Russia, France, etc. then we can use the "Country" column in the CASE statement as shown below-

mysql> Select SUM(CASE WHEN country = &#39;USA&#39; THEN 1 ELSE 0 END) AS USA,
-> SUM(CASE WHEN country = &#39;UK&#39; THEN 1 ELSE 0 END) AS UK,
-> SUM(CASE WHEN country = &#39;INDIA&#39; THEN 1 ELSE 0 END) AS INDIA,
-> SUM(CASE WHEN country = &#39;Russia&#39; THEN 1 ELSE 0 END) AS Russia,
-> SUM(CASE WHEN country = &#39;France&#39; THEN 1 ELSE 0 END) AS France,
-> SUM(CASE WHEN country = &#39;NZ&#39; THEN 1 ELSE 0 END) AS NZ,
-> SUM(CASE WHEN country = &#39;Australia&#39; 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)

Copy after login

The above is the detailed content of How to use column data in MySQL CASE statement?. For more information, please follow other related articles on the PHP Chinese website!