Exclude entries with "0" when using AVG

Question

I have a number mysql database field named "numbers" in which there are 10 entries with numbers ranging from 0-10.

I would like to find the average of this, but excluding all the entries where number = 0. But I would also like to count how many entries there are - including the ones where number = 0.

So I can't just add a WHERE numbers!=0, as that'd give a wrong result in my COUNT.

So I need somthing like.

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

Answer 1

How about this?

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

Notice how this method doesn't force you to use a where clause, in case you want to use this as part of a larger query where you don't want to exclude zero values in general.

Also, by the way, avg skips null values, so in the above example we used nullif to turn 0 values into null values. If you use null values to represent values that shouldn't be taken into account for the average (for example, if 0 is a legitimate value), then just use avg(field).