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

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

WBOY

Release： 2023-09-06 13:21:11

forward

608 people have browsed it

当没有匹配行时，如何将 MySQL SUM() 函数的输出自定义为 0 而不是 NULL？

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)

Copy after login

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 &lsquo;SUM(daily_typing_pages)&rsquo;FROM employee_tbl WHERE Name = &lsquo;Mohan&rsquo;;
+-------------------------+
| SUM(daily_typing_pages) |
+-------------------------+
|           0             |
+-------------------------+
1 row in set (0.00 sec)

Copy after login

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!