title: Strange MySQL AVG() anomaly NULL values
body: <p>What I'am doing :</p>
  
  <pre><code>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);
  </code></pre>
  
  <p><strong>OUTPUT I EXPECTED :</strong> </p>
  
  <blockquote>
    <p>AVG = (10+20+30)/5 = 12</p>
  </blockquote>
  
  <p><strong>OUTPUT OF MYSQL :</strong></p>
  
  <blockquote>
    <p>AVG = (10+20+30)/3 = 20</p>
  </blockquote>
  
  <p>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) </p>
  
  <p>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.</p>
  
  <p>Thank you</p>