CODEWITHSUNDEEP
mysql
Palendrone
Palendrone

Reputation: 363

MySQL Average Excluding some NULL Values

Can't seem to figure this out for the life of me although the logic is quite simple.

I have a small MySQL database that contains the following fields:

I have setup a view in MySQL to report out what the average weight of each variety is using SUM(weight)/SUM(picked) BUT.... sometimes the weight is not input, therefore I'd like to exclude the sum of picked in the average as to not give a false average.

I was using:

SELECT variety, if(weight="", sum(weight)/sum(picked),0) As AvgWeight
FROM `harvest`
GROUP BY variety

Can someone point me in the right direction please?

| picked| weight|
| :---: |  :---:|
|    10 |    20 |
|    10 |    30 |
|    10 |       |

I would want the above to result in AvgWeight as 2.0 and not 1.6666667

Upvotes: 0

Views: 113

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I am going to assume that you want the average as 2.5, not 2.0. If so, calculate the average using conditional aggregations:

select variety,
       (sum(case when weight > 0 then weight else 0 end) /
        sum(case when weight > 0 then picked else 0 end)
       ) as average
from harvest
group by variety;

The expression weight > 0 will eliminate both NULL values and blank values (although if weight is defined as a numeric type, the value cannot be blank).

Upvotes: 2

Hituptony
Hituptony

Reputation: 2860

do a coalesce(weight, 0)

This will return the first non-null integer for your comparison and will return the row as part of the count without messing with your final aggregations.

Upvotes: 0

Related Questions