CODEWITHSUNDEEP
mysqlaverage
Nerfino
Nerfino

Reputation: 31

MySQL: how to get average of positive values only?

Suppose I have INT column, and I am using -1 to signify that no data was available at the time of the INSERT. I'd like to get an AVG of all values of this column that are 0 or larger.

Is this possible?

Thanks.

I forgot to mention that I'm doing this alongside other AVG's, so it's select avg(a), avg(b), avg(d) from tab; ... so I can't use b>0... because the others need to use the row data regardless of this one column's data being -1.

It occurs to me though that I could augment the AVG result e.g. if it would normally be (4 + 5 + -1 + -1 + 6) / 5. But if I know how many -1's there are I could "fix" the result to exclude them.

Upvotes: 3

Views: 7194

Answers (4)

brenjt
brenjt

Reputation: 16297

Could you do something as such 

SUM(column) / COUNT(*) as Average FROM 'table' WHERE 'column' >= 0

Upvotes: 0

Kokos
Kokos

Reputation: 9121

SELECT AVG(`field`) FROM `table` WHERE `field` >= 0

Upvotes: 4

Abhay
Abhay

Reputation: 6645

This might help:

If you want to ignore the -1 values from the average:

SELECT AVG(`a`), AVG(IF(`b` > -1, `b`, NULL)), AVG(`c`) FROM `t`;

If you want to consider the -1 values in the average:

SELECT AVG(`a`), AVG(IF(`b` > -1, `b`, 0)), AVG(`c`) FROM `t`;

I've assumed dummy column- and table- names and assumed column b as the one for which you want to consider only values >= 0. Please feel free to put in names as per your schema.

Upvotes: 5

Ben
Ben

Reputation: 1216

Please use this:

SELECT    AVG(Column),   
 SUM(IF(Column>0))/COUNT(IF(Column>0))
 FROM Table

Upvotes: 2

Related Questions