CODEWITHSUNDEEP
mysqlsql
Tizianoreica
Tizianoreica

Reputation: 2236

AVG of Values except 0 (except 0 is only value) in SQL

I'd like to have AVG excluding 0s from a list of values that can contain them, except in the case that 0 is the only values available (or at least there's just one row and the values is exactly 0).

At the moment I use

SELECT AVG (NULLIF(feature, 0)) 
from cluster_density_feat 
where cluster_id=1;

but it returns NULL if feature value cluster_id has only one row and his feature values is 0 or if every row of this cluster_id has only zero values.

How can I achieve this?

Upvotes: 1

Views: 756

Answers (3)

Jeremy C.
Jeremy C.

Reputation: 2465

You can use a WHERE clause to exclude the 0 values, and if you want to get 0 if there are no other values than 0 use coalesce to get 0, COALESCE is a function that will either get the first value between the brackets if it's not NULL and will return the second value if the first one is NULL

SELECT COALESCE(AVG(feature),0) 
from cluster_density_feat 
where cluster_id=1
AND NOT feature = 0

Upvotes: 2

axiac
axiac

Reputation: 72226

AVG() uses all the values from the group, it cannot exclude the 0s. You should exclude them using a WHERE clause.

SELECT IFNULL(AVG(feature), 0)
FROM cluster_density_feat 
WHERE cluster_id = 1
   AND feature <> 0

If all the values in the feature column are 0, the feature <> 0 condition produces an empty set of rows and AVG() returns NULL. The function IFNULL() returns its second argument when the first one is NULL.

Upvotes: 3

Matt
Matt

Reputation: 15071

Don't include feature when it is 0.

SELECT AVG(feature)
FROM cluster_density_feat 
WHERE cluster_id = 1
AND feature != 0;

Upvotes: 0

Related Questions