How MySQL SUM() function evaluates if it is used with SELECT statement that returns no matching rows?

MySQLMySQLi Database

When MySQL SUM() function used with SELECT statement that returns no matching rows then there is nothing to evaluate and it returns NULL as output. Sometimes, we thought it must return 0 as output but 0 is a number itself and for no matching rows it not significant to return 0 hence it returns NULL. To understand the above concept, consider an ‘employee_tbl’ table, which is having 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, MySQL SUM() function returns NULL when we run the following query to find the total number of pages typed by ‘Mohan’, the name which is not in the ‘Name’ column −

mysql> SELECT SUM(daily_typing_pages) FROM employee_tbl WHERE Name = ‘Mohan’;
+-------------------------+
| SUM(daily_typing_pages) |
+-------------------------+
|          NULL           |
+-------------------------+
1 row in set (0.00 sec)
raja
Published on 21-Feb-2018 07:18:23
Advertisements