Sum, Avg, Max, Min, Count of NULL values

Question

In MySQL SELECT 2+NULL FROM tbl_name will return NULL, since MySQL cannot interpret NULL as a number.

But why will SELECT SUM(quantity) FROM tbl_name not return NULL if just one of the values is NULL? The same goes for MIN, MAX, AVG, etc. Since MySQL doesn't know what NULL could be, shouldn't it return NULL for all the specified functions?

Answer 1

To be simple,

2 + NULL, probably deals with one row only...

Where-as SUM( 2 and NULL ) will be with 2 different rows. So, NULLs are simply ignored while you group! What ever happens with null with the same row only will be resolved into NULL again.

So

COUNT(NULL,1) = 1 (not 2)
MAX(NULL,1) = 1
MIN(NULL,1) = 1
AVG(NULL,1) = 1 (not .5)

This behaviour is same in most of the DBMS versions!

Answer 2

This is a good question, and one that does not have a good answer. The treatment of NULL in your two examples is different.

The fundamental problem is what NULL means. Commonly, it is used to denote missing values. However, in the ANSI standard, it stands for an unknown value. I'm sure philosophers could devote tomes to the difference between "missing" and "unknown".

In a simple expression (boolean or arithmetic or scalar of another sort), ANSI defines the result of "unknown" in almost all cases where any of the operands are "unknown". There are some exceptions: NULL AND FALSE is false and NULL IS NULL is true, but these are rare.

For the aggregation operations, think of SUM() as "sum all the known values", and so on. SUM() treats NULL values differently from +. But, this behavior is also standard so that is how all databases work.

If you want a NULL value for an aggregation when any of its operands is NULL, then you need to use CASE. I think the easiest way for a single column is:

(CASE WHEN COUNT(col) = COUNT(*) THEN SUM(COL) END)

Answer 3

According to the MySQL Reference, NULL values are ignored in the aggregate functions. Here is the direct quote from the page:

Unless otherwise stated, group functions ignore NULL values.