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?

Solution 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

Solution 2

You could probably control that via the WHERE clause:

select avg( field ) from table where field > 0

Solution 3

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

Comments

[Oranges13]

Thanks, this will probably be the best option because yes, I do have values in the other columnns!

[Oranges13]

That works, except there are other fields I wish to pull at the same time as one big average.

[Oranges13]

Only if the rows where field is greater than zero are the ones I'm worried about. Unfortunately in this case, there are 3 other columns I need to check where that may not be the case.

[Avishai]

this works great -- especially when you have more than 1 aggregate function in your query

[Matthew Clark]

Yeesh. Changing existing data to make a SELECT work? I think I'm gonna puke...

[CJL89]

Thanks! I was struggling with this one for a couple of minutes.