AVG command with WHERE statement

[Marco_Develops]

Hello All,

I am trying to calculate the Average of a column, but i want it to ignore all values that are equal to 0.

This currently what I have right now: 

stats avg(ComplianceScore) as CS by GeoLocation

But I need it to calculate AVG only if Compliance Score is not Zero. 

Thank you,

Marco

[ITWhisperer · Accepted solution]

Set the zeros to null so they aren't included in the average

| eval ComplianceScore=if(ComplianceScore==0,null(),ComplianceScore)
| stats avg(ComplianceScore) as CS by GeoLocation

[Marco_Develops]

Thank you! Exactly What I needed... Very clever. 😀 @ITWhisperer 

[richgalloway]

I think you'll have to create a custom command to do that.