- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
- Related Questions & Answers
- 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 SUBSTRING() function be used with FROM and FOR keywords?
- How Groups function can be used in MySQL SELECT clause?
- How can I use IFNULL() function at the place of COALESCE() function in MySQL?
- How can I customize the output of MySQL SUM() function to 0 instead of NULL when there are no matching rows?
- How MySQL aggregate functions can be combined with MySQL IF() function?
- How can we use MySQL SUM() function?
- How to convert MySQL null to 0 using COALESCE() function?
- How can we use MySQL SUM() function with HAVING clause?
- What is MySQL STRCMP() function and what would be the output of this function?
- Which MySQL function can be used to append values of a column with single quotes?