Strange MySQL AVG() anomaly NULL values

Question

What I'am doing :

create table sample (id INT(10) PRIMARY KEY AUTO_INCREMENT,name varchar(255),marks INT(10));

insert into sample (name,marks) VALUES('sam',10);
insert into sample (name,marks) VALUES('sam',20);
insert into sample (name,marks) VALUES('sam',NULL);
insert into sample (name,marks) VALUES('sam',NULL);
insert into sample (name,marks) VALUES('sam',30);

select AVG(marks) from sample GROUP BY(name);

OUTPUT I EXPECTED :

AVG = (10+20+30)/5 = 12

OUTPUT OF MYSQL :

AVG = (10+20+30)/3 = 20

Ideally what i wanted is that MYSQL should get the sum of 5 rows and divide it by 5 , but it only divides by 3 (the non-NULL rows)

Why does this occur and what can i do to get the correct AVG ie 60/5 ? PS: I cannot make the marks field NOT NULL , in my db design the marks field is allowed to be NULL.

Thank you

Answer 1

This is the correct behavior, because NULL is not the same as the number 0.
This might surprise some non-english speakers, because in many languages "null" is equivalent to "zero".

Conceptually, NULL refers to an “unknown value” and as such it is treated differently from other values. That is why aggregate functions like AVG() ignore NULLs.

AVG() calculates the average over all "known" values only. (= that are not NULL)

From the MySQL docs:

Unless otherwise stated, group functions ignore NULL values.

Also, read about the concept of NULLs in Section "3.3.4.6 Working with NULL Values" of the MySQL manual.

To get what you want, you might do

SELECT AVG(IFNULL(marks, 0)) 
FROM sample 
GROUP BY name;

IFNULL() returns the second argument for calculations if the value is NULL or passes through the value otherwise.

---

There are more common misunderstandings regarding the concept of NULL. These are also explained in Section "5.5.3 Problems with NULL" of the manual:

• In SQL, the NULL value is never true in comparison to any other value, even NULL. An expression that contains NULL always produces a NULL value unless otherwise indicated in the documentation for the operators and functions involved in the expression.
  
  i.e.: NULL == 0 results in NULL instead of true. Also NULL == NULL results in NULL, instead of true.
• To search for column values that are NULL, you cannot use an expr = NULL test. To look for NULL values, you must use the IS NULL test.
• When using DISTINCT, GROUP BY, or ORDER BY, all NULL values are regarded as equal.
• When using ORDER BY, NULL values are presented first, or last if you specify DESC to sort in descending order.
• For some data types, MySQL handles NULL values specially. If you insert NULL into a TIMESTAMP column, the current date and time is inserted.
• If you insert NULL into an integer or floating-point column that has the AUTO_INCREMENT attribute, the next number in the sequence is inserted.
• A column that has a UNIQUE key defined can still contain multiple NULL values.

Answer 2

Try:

select avg(case marks when null then 0 else marks end) from sample group by name;

Or try and avoid nulls in the table ;)

Answer 3

You can do this instead:

SELECT SUM(marks) / COUNT(name)
FROM sample 
GROUP BY name;

Answer 4

That's the normal behaviour, since NULL is not zero. How do you make the average of 5 + NULL? So MySQL is taking only the rows that can be averaged.

Appart from the correct answers other users have already given you, you can also use the COALESCE function, which returns the first non-NULL value you specify in the list, so you can replace the NULL one with the one you like:

    SELECT AVG(COALESCE(marks,0)) FROM sample GROUP BY(name);