Mysql AVG to ignore zero

mysql null zero average
Oranges13 picture Oranges13 · Mar 17, 2011 · Viewed 19.8k times · Source

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

Martin Smith picture Martin Smith · Mar 17, 2011

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

Related questions

How set 0 with MAX function when it is NULL?

I would like to understand how to set 0 value of the attribute when it is NULL with MAX function. For example: Name columns: number - date Values: 10 - 2012-04-04 11 - 2012-04-04 12 - 2012-04-04 13 - 2012-04-15 14 - 2012…

Read More
Empty string inserting a zero, not a null

My insert statement looks like this: INSERT INTO foo (bar) VALUES (''); The bar field was created like so: bar INT(11) COLLATION: (NULL) NULL: YES DEFAULT: (NULL) MySQL version: 5.1. Shouldn’t an empty string insert a NULL? I’m not …

Read More
MySQL: selecting rows where a column is null

I'm having a problem where when I try to select the rows that have a NULL for a certain column, it returns an empty set. However, when I look at the table in phpMyAdmin, it says null for most of …

Read More