How to combine MySQL aggregate function with MySQL IF() function?-Mysql Tutorial-php.cn

How to combine MySQL aggregate function with MySQL IF() function?

WBOY

Release： 2023-08-27 13:37:13

forward

787 people have browsed it

MySQL聚合函数如何与MySQL IF()函数结合？

Combining MySQL aggregate functions with MySQL IF() function can be very helpful to get the specific output we want. Consider the following queries which combine SUM() and COUNT() aggregate functions with IF() function.

Example

mysql> Select SUM(IF(Language = &#39;English&#39;, 1, 0)) As English, SUM(IF(Language <> &#39;English&#39;,1,0)) AS "Non-English" from Students;
+---------+-------------+
| English | Non-English |
+---------+-------------+
| 5       | 4           |
+---------+-------------+
1 row in set (0.00 sec)

Copy after login

The above query uses the SUM() aggregate function with the IF() function to obtain English native students and non-English speaking students from the "Students" table. The output of native English speaking students.

mysql> Select COUNT(IF(country = &#39;USA&#39;, 1, NULL))AS USA,
    -> COUNT(IF(country = &#39;UK&#39;, 1, NULL))AS UK,
    -> COUNT(IF(country = &#39;France&#39;, 1, NULL))AS France,
    -> COUNT(IF(country = &#39;Russia&#39;, 1, NULL))AS Russia,
    -> COUNT(IF(country = &#39;Australia&#39;, 1, NULL))AS Australia,
    -> COUNT(IF(country = &#39;INDIA&#39;, 1, NULL))AS INDIA,
    -> COUNT(IF(country = &#39;NZ&#39;, 1, NULL))AS NZ FROM Students;
+-----+----+--------+--------+-----------+-------+----+
| USA | UK | France | Russia | Australia | INDIA | NZ |
+-----+----+--------+--------+-----------+-------+----+
| 2   | 1  | 1      | 1      | 1         | 2     | 1  |
+-----+----+--------+--------+-----------+-------+----+
1 row in set (0.07 sec)

Copy after login

The above query combines the COUNT() aggregate function with the IF() function to get the output of the number of countries from the "Students" table.

The above is the detailed content of How to combine MySQL aggregate function with MySQL IF() function?. For more information, please follow other related articles on the PHP Chinese website!