How can MySQL COALESCE() function be used with MySQL SUM() function to customize the output?

When MySQL SUM() function got a column, having no values, an argument then it will return NULL, rather than 0, as output. But if we want to customize this output to show 0 as output then we can use MySQL COALESCE() function which accepts two arguments and returns the second argument if the first argument is NULL, otherwise, it returns the first argument. To illustrate it, we are taking the example of ‘Tender’ table having the following data −

mysql> Select * from tender;

+----+---------------+--------------+
| Sr | CompanyName   | Tender_value |
+----+---------------+--------------+
| 1  | Abc Corp.     | 250.369003   |
| 2  | Khaitan Corp. | 265.588989   |
| 3  | Singla group. | 220.255997   |
| 4  | Hero group.   | 221.253006   |
| 5  | Honda group   | NULL         |
+----+---------------+--------------+

5 rows in set (0.00 sec)

MySQL SUM() function returns NULL when we try to find the total tender value quoted by ‘Honda Group’ because it is having no value in the column.

mysql> Select SUM(Tender_value) From Tender Where CompanyName = 'Honda Group';

+-------------------+
| SUM(Tender_value) |
+-------------------+
|              NULL |
+-------------------+

1 row in set (0.00 sec)

But, suppose if we want to customize this output from NULL to 0 then we can use COALESCE function with SUM() to find the total tender value quoted by ‘Honda Group’.

mysql> Select COALESCE(SUM(Tender_value),0) From Tender Where CompanyName = 'Honda Group';

+-------------------------------+
| COALESCE(SUM(Tender_value),0) |
+-------------------------------+
|                      0.000000 |
+-------------------------------+

1 row in set (0.00 sec)

Now, MySQL SUM() function returns 0 when we use COALESCE function with SUM() to find the total number of pages typed by ‘Mohan’, the name which is not in the ‘Name’ column −

mysql> SELECT COALESCE(SUM(daily_typing_pages),0) FROM employee_tbl WHERE Name = ‘Mohan’;

+-------------------------+
| SUM(daily_typing_pages) |
+-------------------------+
|                       0 |
+-------------------------+

1 row in set (0.00 sec)

From the above result sets, it is clear that MySQL SUM() function will return NULL if there would be no values in the column irrespective of its data type.

Paul Richard

Published on 20-Feb-2018 09:20:33

Previous Page Print Page