How MySQL aggregate functions can be combined 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 = 'English', 1, 0)) As English, SUM(IF(Language <> 'English',1,0)) AS "Non-English" from Students;
+---------+-------------+
| English | Non-English |
+---------+-------------+
| 5       | 4           |
+---------+-------------+
1 row in set (0.00 sec)

The above query combines SUM() aggregate function with IF() function to get the output of English speaking students and non-English speaking students from ‘Students’ table.

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 COUNT() aggregate function with IF() function to get the output of a number of countries from ‘Students’ table.

Moumita

Updated on 11-Feb-2020 06:27:37

• Related Questions & Answers
• How MySQL LTRIM() and RTRIM()functions can be used with WHERE clause?
• Call aggregate function in sort order with MySQL
• How LOCATE() function can be used with MySQL WHERE clause?
• How can CONCAT() function be used with MySQL WHERE clause?
• How wildcard characters can be used with MySQL CONCAT() function?
• How can CONCAT_WS() function be used with MySQL WHERE clause?
• How can MySQL REPLACE() function be used with WHERE clause?
• How can MySQL COALESCE() function be used with MySQL SUM() function to customize the output?
• How MySQL CONCAT() function, applied to the column/s of a table, can be combined with the column/s of other tables?
• How can MySQL SUBSTRING() function be used with FROM and FOR keywords?
• How to get MySQL combined field result?
• How Groups function can be used in MySQL SELECT clause?
• How can we combine functions in MySQL?
• Get the maximum value of a column with MySQL Aggregate function
• How MySQL IF statement can be used in a stored procedure?