Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Strange MySQL AVG() anomaly NULL values

Tags:

sql

null

mysql

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

like image 976
Nishant Jani Avatar asked Dec 24 '12 11:12

Nishant Jani


People also ask

Does AVG consider NULL values?

AVG() function does not consider the NULL values during its calculation.

Can Avg function handle nulls?

The AVG function is an aggregate function that calculates the average value of all non-NULL values within a group. By default, the AVG function uses ALL clause whether you specify it or not. It means the AVG function will take all non-NULL values when it calculates the average value.

Does SQL AVG ignore 0?

To exclude entries with “0”, you need to use NULLIF() with function AVG().

How do I select AVG in MySQL?

MySQL AVG() function retrieves the average value of a given expression. If the function does not find a matching row, it returns NULL. Where expr is a given expression. The DISTINCT option can be used to return the average of the distinct values of expr.


4 Answers

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.
like image 88
Kaii Avatar answered Oct 04 '22 08:10

Kaii


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

like image 28
fge Avatar answered Oct 04 '22 07:10

fge


You can do this instead:

SELECT SUM(marks) / COUNT(name)
FROM sample 
GROUP BY name;
like image 35
Mahmoud Gamal Avatar answered Oct 04 '22 07:10

Mahmoud Gamal


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);
like image 42
Pere Avatar answered Oct 04 '22 07:10

Pere