Mysql AVG to ignore zero

Question

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 probably have positive values. How can I tell mySQL to ignore the zeros and only average the actual values?

Answer 1

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

Answer 2

You could probably control that via the WHERE clause:

select avg( field ) from table where field > 0

Answer 3

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;

Answer 4

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