Mysql AVG to ignore zero

I need to perform an avg on a column, but I know that most of the values in that column will be zero. Out of all possible rows, only two will pr开发者_如何学Cobably have positive values. How can I tell mySQL to ignore the zeros and only average the actual values?

Assuming that you might want to not totally exclude such rows (perhaps they have values in other columns you want to aggregate)

SELECT AVG(NULLIF(field ,0)) 
from table

You could probably control that via the WHERE clause:

select avg( field ) from table where field > 0

select avg(your_column) 
from your_table 
where your_column != 0

You can convert zeros to NULL, then AVG() function will work only with not NULL values.

UPDATE table SET column = NULL WHERE column='0';
SELECT AVG(column) FROM table;