How can I customize the output of the MySQL SUM() function to be 0 instead of NULL when there are no matching rows?

We know that the SUM() function will return NULL if there are no matching rows, but sometimes we want it to return zero instead of NULL. To do this, we can use the MySQL COALESCE() function, which accepts two parameters and returns the second parameter if the first parameter is NULL, otherwise it returns the first parameter. To understand the above concept, consider an "employee_tbl" table which has the following records -

mysql> SELECT * FROM employee_tbl;
+------+------+------------+--------------------+
| id   | name | work_date  | daily_typing_pages |
+------+------+------------+--------------------+
| 1    | John | 2007-01-24 |        250         |
| 2    | Ram  | 2007-05-27 |        220         |
| 3    | Jack | 2007-05-06 |        170         |
| 3    | Jack | 2007-04-06 |        100         |
| 4    | Jill | 2007-04-06 |        220         |
| 5    | Zara | 2007-06-06 |        300         |
| 5    | Zara | 2007-02-06 |        350         |
+------+------+------------+--------------------+
7 rows in set (0.00 sec)

Now, when we use the COALESCE function along with the SUM() function to find the total number of pages entered by "Mohan" When, MySQL's SUM() function returns 0, and the name "Mohan" is not in the "Name" column −

mysql> SELECT COALESCE(SUM(daily_typing_pages),0)AS ‘SUM(daily_typing_pages)’FROM employee_tbl WHERE Name = ‘Mohan’;
+-------------------------+
| SUM(daily_typing_pages) |
+-------------------------+
|           0             |
+-------------------------+
1 row in set (0.00 sec)

The above is the detailed content of How can I customize the output of the MySQL SUM() function to be 0 instead of NULL when there are no matching rows?. For more information, please follow other related articles on the PHP Chinese website!