Exclude recordings with "0" when using AVG

Question

I have a mysql database field named "numbers" in which there are 10 records with numbers from 0 to 10.

I would like to find the average value of this, but excluding all entries where number = 0. But I would also like to calculate how many entries there are, including numbers in which number = 0.

Therefore, I can’t just add the numbers WHERE! = 0, as this would give the wrong result in my COUNT.

So I need something like.

AVG(if(numbers!=0)) AS average 

Answer 1

How about this?

 select avg(nullif(field, 0)) from table; 

Note that this method does not force you to use the where clause if you want to use it as part of a larger query in which you do not want to exclude null values ​​at all.

Also, by the way, avg skips null values, so in the example above we used nullif to turn 0 values ​​to null values. If you use null values ​​to represent values ​​that should not be taken into account for the mean (for example, if 0 is a legal value), just use avg(field) .

Answer 2

 SELECT avg(case when numbers = 0 then null else numbers end) as average, count(*) FROM your_table 

Since NULL values ​​are not used for avg (), this should do it.