Nishant Jani Nishant Jani - 1 year ago 88
MySQL Question

Strange MySQL AVG() anomaly NULL values

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);


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


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 Source

This is the correct behavior, because NULL is not the same as the number 0.

Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values. That is why aggregate functions like AVG() ignore NULLs.

AVG() calculates the average over all specified 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 " 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.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download