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

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)

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 = 'USA', 1, NULL))AS USA,
    -> COUNT(IF(country = 'UK', 1, NULL))AS UK,
    -> COUNT(IF(country = 'France', 1, NULL))AS France,
    -> COUNT(IF(country = 'Russia', 1, NULL))AS Russia,
    -> COUNT(IF(country = 'Australia', 1, NULL))AS Australia,
    -> COUNT(IF(country = 'INDIA', 1, NULL))AS INDIA,
    -> COUNT(IF(country = 'NZ', 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)

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!