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?

score 99 · Accepted Answer · answered Mar 17 '11 at 15:07

99

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

answered Mar 17 '11 at 15:07

Martin Smith

438,706
87
741
845

Thanks, this will probably be the best option because yes, I do have values in the other columnns! – Oranges13 Mar 17 '11 at 18:18
2

this works great -- especially when you have more than 1 aggregate function in your query – Avishai Jan 01 '13 at 23:42
Thanks! I was struggling with this one for a couple of minutes. – CJL89 May 28 '21 at 19:16

score 5 · Answer 2 · answered Mar 17 '11 at 15:05

5

You could probably control that via the WHERE clause:

select avg( field ) from table where field > 0

answered Mar 17 '11 at 15:05

Mark Wilkins

40,729
5
57
110

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. – Oranges13 Mar 17 '11 at 19:53

score 1 · Answer 3 · answered Mar 17 '11 at 15:09

1

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

answered Mar 17 '11 at 15:09

Ike Walker

64,401
14
110
109

That works, except there are other fields I wish to pull at the same time as one big average. – Oranges13 Mar 17 '11 at 19:52

score 0 · Answer 4 · edited Oct 16 '12 at 18:28

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;

edited Oct 16 '12 at 18:28

Thomas Berger

1,860
13
26

answered Oct 16 '12 at 18:22

Barvajz

25
1

5

Yeesh. Changing existing data to make a `SELECT` work? I think I'm gonna puke... – Matthew Clark Dec 23 '14 at 20:39

Mysql AVG to ignore zero

4 Answers4

Linked