How MySQL SUM() function evaluates if the column having NULL values too?

Suppose if we are calculating the sum of the values of a column which also have NULL values then MySQL SUM() function ignores the NULL values and does the sum of the rest of the values. To understand it, consider the following example of table ‘employee’, having following details −

mysql> Select * from Employee;
+----+--------+--------+
| ID | Name   | Salary |
+----+--------+--------+
| 1  | Gaurav | 50000  |
| 2  | Rahul  | 20000  |
| 3  | Advik  | 25000  |
| 4  | Aarav  | 65000  |
| 5  | Ram    | 20000  |
| 6  | Mohan  | 30000  |
| 7  | Aryan  | NULL   |
| 8  | Vinay  | NULL   |
+----+--------+--------+
8 rows in set (0.00 sec)

Now, suppose if we want to have the total salary of the employees from above table then while calculating it with SUM() function, it ignores the NULL values. The following query will produce the required result set −

mysql> Select SUM(Salary) from Employee;
+-------------+
| SUM(Salary) |
+-------------+
| 210000      |
+-------------+
1 row in set (0.00 sec)

It can also be verified with the help of the following query −

mysql> Select SUM(Salary) from Employee WHERE Salary IS NOT NULL;
+-------------+
| SUM(Salary) |
+-------------+
|   210000    |
+-------------+
1 row in set (0.00 sec)

George John

Published on 21-Feb-2018 07:38:23

• Related Questions & Answers
• How MySQL SUM() function evaluates if it got the column, having character data type, as its argument?
• How MySQL stored function evaluates if it got NULL value while using the dynamic values from a table?
• How MySQL evaluates if I will use an expression within SUM() function?
• What would be the output of MySQL SUM() function if a column having no values has been passed as its argument?
• How MySQL evaluates if we use EXISTS operator with the subquery that returns NULL?
• How MySQL evaluates if I store date along with time value in a column having DATE data type?
• What MySQL returns when we use DISTINCT clause with the column having multiple NULL values?
• How MySQL evaluates when I use a conditional expression within SUM() function?
• What MySQL COUNT() function returns if there are some NULL values stored in a column also?
• How MySQL SUM() function evaluates if it is used with SELECT statement that returns no matching rows?
• How can we use MySQL SUM() function with HAVING clause?
• How can we use MySQL SUM() function to calculate the sum of only dissimilar values of the column?
• Sum the values in a column in MySQL?
• MySQL query to display only the column values with corresponding column having whitespace
• MySQL update column to NULL for blank values