Skip to content

Latest commit

 

History

History
85 lines (62 loc) · 2.75 KB

sumif-aggregation-function.md

File metadata and controls

85 lines (62 loc) · 2.75 KB
Raw
title description ms.reviewer ms.topic ms.date
sumif() (aggregation function)
Learn how to use the sumif() (aggregation function) function to calculate the sum of an expression value in records for which the predicate evaluates to true.
alexans
reference
11/13/2023

sumif() (aggregation function)

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

[!INCLUDE ignore-nulls]

[!INCLUDE data-explorer-agg-function-summarize-note]

You can also use the sum() function, which sums rows without predicate expression.

Syntax

sumif(expr,predicate)

[!INCLUDE syntax-conventions-note]

Parameters

Name Type Required Description
expr string ✔️ The expression used for the aggregation calculation.
predicate string ✔️ The expression used to filter rows. If the predicate evaluates to true, the row will be included in the result.

Returns

Returns the sum of expr for which predicate evaluates to true.

Example showing the sum of damages based on no casualty count

This example shows the sum total damage for storms without casualties.

[!div class="nextstepaction"] Run the query

StormEvents
| summarize DamageNoCasualties=sumif((DamageCrops+DamageProperty),(DeathsDirect+DeathsIndirect)==0) by State

Output

The results table shown includes only the first 10 rows.

State DamageNoCasualties
TEXAS 242638700
KANSAS 407360000
IOWA 135353700
ILLINOIS 120394500
MISSOURI 1096077450
GEORGIA 1077448750
MINNESOTA 230407300
WISCONSIN 241550000
NEBRASKA 70356050
NEW YORK 58054000
... ...

Example showing the sum of birth dates

This example shows the sum of the birth dates for all names that have more than 4 letters.

let T = datatable(name:string, day_of_birth:long)
[
   "John", 9,
   "Paul", 18,
   "George", 25,
   "Ringo", 7
];
T
| summarize sumif(day_of_birth, strlen(name) > 4)

Output

sumif_day_of_birth
32