avgif() (aggregation function)

Calculates the average of expr in records for which predicate evaluates to true.


This function is used in conjunction with the summarize operator.


avgif (expr, predicate)

Learn more about syntax conventions.


Name Type Required Description
expr string The expression used for aggregation calculation. Records with null values are ignored and not included in the calculation.
predicate string The predicate that if true, the expr calculated value will be added to the average.


Returns the average value of expr in records where predicate evaluates to true.


This example calculates the average damage by state in cases where there was any damage.

| summarize Averagedamage=tolong(avg( DamageCrops)),AverageWhenDamage=tolong(avgif(DamageCrops,DamageCrops >0)) by State

The results table shown includes only the first 10 rows.

State Averagedamage Averagewhendamage
TEXAS 7524 491291
KANSAS 15366 695021
IOWA 4332 28203
ILLINOIS 44568 2574757
MISSOURI 340719 8806281
GEORGIA 490702 57239005
MINNESOTA 2835 144175
WISCONSIN 17764 438188
NEBRASKA 21366 187726
NEW YORK 5 10000
... ... ...