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

回答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

回答2:

You could probably control that via the WHERE clause:

select avg( field ) from table where field > 0

回答3:

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

回答4:

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;