[MySQL] How to include NULL value in AVG function

 

When you need to include a NULL value?

From now on, if there is a null value in the SQL table as shown in the following table, we have to be careful. Because there is a problem, not a problem. It's even more concerning that it's not considered a problem.

ID	NUM	Name
1	42	A
2	32	B
3	NULL	V

SELECT COUNT(*) NAME FROM table; 

-- result : 3

SELECT COUNT(NUM) NAME FROM table; 

-- result : 2

If we look at the table above, SQL query as follows, the following results will be obtained. The reason for this difference is that there is a difference between counting and not counting the NULL value. Like COUNT function, AVG function in SQL also doesn't counts NULL values. A null value does not act as a row or become zero. It means the whole row is completely absent or nonexistent. That's why when we do SELECT AVG(NUM), the result is (42+32)/2, not (42+32+0)/3). 

 

If we want the average to be calculated in the first way(= (42+32)/2 ), neglecting Null row, we can just declare SELECT AVG(NUM) as it is. However, like the latter, if we want the null value to be zero and include it in the avg denominator as an object at the same time, let's use SELECT SUM(NUM)/COUNT(*) instead of SELECT AVG(NUM). Conclusion: The numerator and denominator values should be obtained separately and averaged by dividing them.