Exclude entries with "0" when using AVG

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 a开发者_C百科dd 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

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

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

As NULL values are not used for avg() this should do it.