Aggregation functions

• 1: Aggregation Functions
• 2: arg_max() (aggregation function)
• 3: arg_min() (aggregation function)
• 4: avg() (aggregation function)
• 5: avgif() (aggregation function)
• 6: binary_all_and() (aggregation function)
• 7: binary_all_or() (aggregation function)
• 8: binary_all_xor() (aggregation function)
• 9: buildschema() (aggregation function)
• 10: count_distinct() (aggregation function) - (preview)
• 11: count_distinctif() (aggregation function) - (preview)
• 12: count() (aggregation function)
• 13: countif() (aggregation function)
• 14: dcount() (aggregation function)
• 15: dcountif() (aggregation function)
• 16: hll_if() (aggregation function)
• 17: hll_merge() (aggregation function)
• 18: hll() (aggregation function)
• 19: make_bag_if() (aggregation function)
• 20: make_bag() (aggregation function)
• 21: make_list_if() (aggregation function)
• 22: make_list_with_nulls() (aggregation function)
• 23: make_list() (aggregation function)
• 24: make_set_if() (aggregation function)
• 25: make_set() (aggregation function)
• 26: max() (aggregation function)
• 27: maxif() (aggregation function)
• 28: min() (aggregation function)
• 29: minif() (aggregation function)
• 30: percentile(), percentiles()
• 31: percentilew(), percentilesw()
• 32: stdev() (aggregation function)
• 33: stdevif() (aggregation function)
• 34: stdevp() (aggregation function)
• 35: sum() (aggregation function)
• 36: sumif() (aggregation function)
• 37: take_any() (aggregation function)
• 38: take_anyif() (aggregation function)
• 39: tdigest_merge() (aggregation functions)
• 40: tdigest() (aggregation function)
• 41: variance() (aggregation function)
• 42: varianceif() (aggregation function)
• 43: variancep() (aggregation function)

1 - Aggregation Functions

An aggregation function performs a calculation on a set of values, and returns a single value. These functions are used in conjunction with the summarize operator. This article lists all available aggregation functions grouped by type. For scalar functions, see Scalar function types.

Binary functions

Dynamic functions

Row selector functions

Statistical functions

2 - arg_max() (aggregation function)

Finds a row in the table that maximizes the specified expression. It returns all columns of the input table or specified columns.

Syntax

arg_max (ExprToMaximize, * | ExprToReturn [, …])

Parameters

Returns

Returns a row in the table that maximizes the specified expression ExprToMaximize, and the values of columns specified in ExprToReturn.

Examples

Find maximum latitude

The following example finds the maximum latitude of a storm event in each state.

StormEvents 
| summarize arg_max(BeginLat, BeginLocation) by State

Output

The results table displays only the first 10 rows.

Find last state fatal event

The following example finds the last time an event with a direct death happened in each state, showing all the columns.

The query first filters the events to include only those events where there was at least one direct death. Then the query returns the entire row with the most recent StartTime.

StormEvents
| where DeathsDirect > 0
| summarize arg_max(StartTime, *) by State

Output

The results table displays only the first 10 rows and first three columns.

Handle nulls

The following example demonstrates null handling.

datatable(Fruit: string, Color: string, Version: int) [
    "Apple", "Red", 1,
    "Apple", "Green", int(null),
    "Banana", "Yellow", int(null),
    "Banana", "Green", int(null),
    "Pear", "Brown", 1,
    "Pear", "Green", 2,
]
| summarize arg_max(Version, *) by Fruit

Output

Comparison to max()

The arg_max() function differs from the max() function. The arg_max() function allows you to return other columns along with the maximum value, and max() only returns the maximum value itself.

Examples

arg_max()

Find the last time an event with a direct death happened, showing all the columns in the table.

The query first filters the events to only include events where there was at least one direct death. Then the query returns the entire row with the most recent (maximum) StartTime.

StormEvents
| where DeathsDirect > 0
| summarize arg_max(StartTime, *)

The results table returns all the columns for the row containing the highest value in the expression specified.

| StartTime | EndTime | EpisodeId | EventId | State | EventType | … | |–|–|–|–| | 2007-12-31T15:00:00Z | 2007-12-31T15:00:00 | 12688 | 69700 | UTAH | Avalanche | … |

max()

Find the last time an event with a direct death happened.

The query filters events to only include events where there is at least one direct death, and then returns the maximum value for StartTime.

StormEvents
| where DeathsDirect > 0
| summarize max(StartTime)

The results table returns the maximum value of StartTime, without returning other columns for this record.

Related content

• Aggregation function types at a glance
• arg_min function
• max function
• avg function
• percentile function

3 - arg_min() (aggregation function)

Finds a row in the table that minimizes the specified expression. It returns all columns of the input table or specified columns.

Syntax

arg_min (ExprToMinimize, * | ExprToReturn [, …])

Parameters

Null handling

When ExprToMinimize is null for all rows in a table, one row in the table is picked. Otherwise, rows where ExprToMinimize is null are ignored.

Returns

Returns a row in the table that minimizes ExprToMinimize, and the values of columns specified in ExprToReturn. Use or * to return the entire row.

Examples

Find the minimum latitude of a storm event in each state.

StormEvents 
| summarize arg_min(BeginLat, BeginLocation) by State

Output

The results table shown includes only the first 10 rows.

Find the first time an event with a direct death happened in each state, showing all of the columns.

The query first filters the events to only include those where there was at least one direct death. Then the query returns the entire row with the lowest value for StartTime.

StormEvents
| where DeathsDirect > 0
| summarize arg_min(StartTime, *) by State

Output

The results table shown includes only the first 10 rows and first 3 columns.

The following example demonstrates null handling.

datatable(Fruit: string, Color: string, Version: int) [
    "Apple", "Red", 1,
    "Apple", "Green", int(null),
    "Banana", "Yellow", int(null),
    "Banana", "Green", int(null),
    "Pear", "Brown", 1,
    "Pear", "Green", 2,
]
| summarize arg_min(Version, *) by Fruit

Output

Comparison to min()

The arg_min() function differs from the min() function. The arg_min() function allows you to return additional columns along with the minimum value, and min() only returns the minimum value itself.

Examples

arg_min()

Find the first time an event with a direct death happened, showing all the columns in the table.

The query first filters the events to only include those where there was at least one direct death. Then the query returns the entire row with the lowest value for StartTime.

StormEvents
| where DeathsDirect > 0
| summarize arg_min(StartTime, *)

The results table returns all the columns for the row containing the lowest value in the expression specified.

| StartTime | EndTime | EpisodeId | EventId | State | EventType | … | |–|–|–|–| | 2007-01-01T00:00:00Z | 2007-01-22T18:49:00Z | 2408 | 11929 | INDIANA | Flood | … |

min()

Find the first time an event with a direct death happened.

The query filters events to only include those where there is at least one direct death, and then returns the minimum value for StartTime.

StormEvents
| where DeathsDirect > 0
| summarize min(StartTime)

The results table returns the lowest value in the specific column only.

Related content

• min function
• max function
• avg function
• percentile function
• min-of function

4 - avg() (aggregation function)

Calculates the average (arithmetic mean) of expr across the group.

Syntax

avg(expr)

Parameters

Returns

Returns the average value of expr across the group.

Example

The following example returns the average number of damaged crops per state.

StormEvents
| summarize AvgDamageToCrops = avg(DamageCrops) by State

The results table shown includes only the first 10 rows.

Related content

• Aggregation function types at a glance
• min() (aggregation function)
• max() (aggregation function)
• percentile(), percentiles() (aggregation function)

5 - avgif() (aggregation function)

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

Syntax

avgif (expr, predicate)

Parameters

Returns

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

Example

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

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

Output

The results table shown includes only the first 10 rows.

Related content

• Aggregation function types at a glance
• avg() (aggregation function)
• minif() (aggregation function)
• maxif() (aggregation function)

6 - binary_all_and() (aggregation function)

Accumulates values using the binary AND operation for each summarization group, or in total if a group isn’t specified.

Syntax

binary_all_and (expr)

Parameters

Returns

Returns an aggregated value using the binary AND operation over records for each summarization group, or in total if a group isn’t specified.

Example

The following example produces CAFEF00D using binary AND operations:

datatable(num:long)
[
  0xFFFFFFFF, 
  0xFFFFF00F,
  0xCFFFFFFD,
  0xFAFEFFFF,
]
| summarize result = toupper(tohex(binary_all_and(num)))

Output

Related content

• Aggregation function types at a glance
• binary_all_or() (aggregation function)
• binary_all_xor() (aggregation function)
• binary_and()

7 - binary_all_or() (aggregation function)

Accumulates values using the binary OR operation for each summarization group, or in total if a group isn’t specified.

Syntax

binary_all_or (expr)

Parameters

Returns

Returns an aggregated value using the binary OR operation over records for each summarization group, or in total if a group isn’t specified.

Example

The following example produces CAFEF00D using binary OR operations:

datatable(num:long)
[
  0x88888008,
  0x42000000,
  0x00767000,
  0x00000005, 
]
| summarize result = toupper(tohex(binary_all_or(num)))

Output

Related content

• Aggregation function types at a glance
• binary_all_or() (aggregation function)
• binary_all_xor() (aggregation function)
• binary_and()
• binary_or()

8 - binary_all_xor() (aggregation function)

Accumulates values using the binary XOR operation for each summarization group, or in total if a group is not specified.

Syntax

binary_all_xor (expr)

Parameters

Returns

Returns a value that is aggregated using the binary XOR operation over records for each summarization group, or in total if a group isn’t specified.

Example

The following example produces CAFEF00D using binary XOR operations:

datatable(num:long)
[
  0x44404440,
  0x1E1E1E1E,
  0x90ABBA09,
  0x000B105A,
]
| summarize result = toupper(tohex(binary_all_xor(num)))

Output

Related content

• Aggregation function types at a glance
• binary_all_or() (aggregation function)
• binary_all_and() (aggregation function)
• binary_xor()

9 - buildschema() (aggregation function)

Builds the minimal schema that admits all values of DynamicExpr.

Syntax

buildschema (DynamicExpr)

Parameters

Returns

Returns the minimal schema that admits all values of DynamicExpr.

Example

The following example builds a schema based on:

• {"x":1, "y":3.5}
• {"x":"somevalue", "z":[1, 2, 3]}
• {"y":{"w":"zzz"}, "t":["aa", "bb"], "z":["foo"]}

datatable(value: dynamic) [
    dynamic({"x":1, "y":3.5}),
    dynamic({"x":"somevalue", "z":[1, 2, 3]}),
    dynamic({"y":{"w":"zzz"}, "t":["aa", "bb"], "z":["foo"]})
]
| summarize buildschema(value)

Results

Schema breakdown

In the resulting schema:

• The root object is a container with four properties named x, y, z, and t.
• Property x is either type long or type string.
• Property y is either type double or another container with a property w of type string.
• Property z is an array, indicated by the indexer keyword, where each item can be either type long or type string.
• Property t is an array, indicated by the indexer keyword, where each item is a string.
• Every property is implicitly optional, and any array might be empty.

Related content

• Best practices for schema management
• getschema operator
• infer_storage_schema plugin

10 - count_distinct() (aggregation function) - (preview)

Counts unique values specified by the scalar expression per summary group, or the total number of unique values if the summary group is omitted.

If you only need an estimation of unique values count, we recommend using the less resource-consuming dcount aggregation function.

To count only records for which a predicate returns true, use the count_distinctif aggregation function.

Syntax

count_distinct (expr)

Parameters

Returns

Long integer value indicating the number of unique values of expr per summary group.

Example

The following example shows how many types of storm events happened in each state.

Function performance can be degraded when operating on multiple data sources from different clusters.

StormEvents
| summarize UniqueEvents=count_distinct(EventType) by State
| top 5 by UniqueEvents

Output

Related content

• Aggregation function types at a glance
• count_distinctif() (aggregation function)
• count() (aggregation function)
• countof()
• countif() (aggregation function)

11 - count_distinctif() (aggregation function) - (preview)

Conditionally counts unique values specified by the scalar expression per summary group, or the total number of unique values if the summary group is omitted. Only records for which predicate evaluates to true are counted.

If you only need an estimation of unique values count, we recommend using the less resource-consuming dcountif aggregation function.

Syntax

count_distinctif (expr, predicate)

Parameters

Returns

Integer value indicating the number of unique values of expr per summary group, for all records for which the predicate evaluates to true.

Example

The following example shows how many types of death-causing storm events happened in each state. Only storm events with a nonzero count of deaths are counted.

StormEvents
| summarize UniqueFatalEvents=count_distinctif(EventType,(DeathsDirect + DeathsIndirect)>0) by State
| where UniqueFatalEvents > 0
| top 5 by UniqueFatalEvents

Output

Related content

• Aggregation function types at a glance
• count_distinct() (aggregation function)
• countif() (aggregation function)
• dcountif() (aggregation function)

12 - count() (aggregation function)

Counts the number of records per summarization group, or total if summarization is done without grouping.

To only count records for which a predicate returns true, use countif().

Syntax

count()

Returns

Returns a count of the records per summarization group, or in total if summarization is done without grouping.

Example

The following example returns a count of events in states:

StormEvents
| summarize Count=count() by State

Output

Related content

• Aggregation function types at a glance
• countof()
• countif() (aggregation function)
• count_distinct() (aggregation function)
• bin_at()

13 - countif() (aggregation function)

Counts the rows in which predicate evaluates to true.

Syntax

countif (predicate)

Parameters

Returns

Returns a count of rows in which predicate evaluates to true.

Examples

Count storms by state

This example shows the number of storms with damage to crops by state.

StormEvents
| summarize TotalCount=count(),TotalWithDamage=countif(DamageCrops >0) by State

The results table shown includes only the first 10 rows.

Count based on string length

This example shows the number of names with more than four letters.

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

Output

Related content

• Aggregation function types at a glance
• count_distinctif() (aggregation function) - (preview)
• dcountif() (aggregation function)
• count()

14 - dcount() (aggregation function)

Calculates an estimate of the number of distinct values that are taken by a scalar expression in the summary group.

Syntax

dcount (expr[, accuracy])

Parameters

Returns

Returns an estimate of the number of distinct values of expr in the group.

Example

This example shows how many types of storm events happened in each state.

StormEvents
| summarize DifferentEvents=dcount(EventType) by State
| order by DifferentEvents

The results table shown includes only the first 10 rows.

Estimation accuracy

Related content

• Aggregation function types at a glance
• dcountif() (aggregation function)
• count()
• count_distinct() (aggregation function)

15 - dcountif() (aggregation function)

Estimates the number of distinct values of expr for rows in which predicate evaluates to true.

Syntax

dcountif (expr, predicate, [, accuracy])

Parameters

Returns

Returns an estimate of the number of distinct values of expr for rows in which predicate evaluates to true.

Example

This example shows how many types of fatal storm events happened in each state.

StormEvents
| summarize DifferentFatalEvents=dcountif(EventType,(DeathsDirect + DeathsIndirect)>0) by State
| where DifferentFatalEvents > 0
| order by DifferentFatalEvents 

The results table shown includes only the first 10 rows.

Estimation accuracy

Related content

• Aggregation function types at a glance
• dcount() (aggregation function)
• countif() (aggregation function)
• count_distinctif() (aggregation function)

16 - hll_if() (aggregation function)

Calculates the intermediate results of dcount in records for which the predicate evaluates to true.

Read about the underlying algorithm (HyperLogLog) and the estimation accuracy.

Syntax

hll_if (expr, predicate [, accuracy])

Parameters

Returns

Returns the intermediate results of distinct count of Expr for which Predicate evaluates to true.

Examples

The following query results in the number of unique flood event sources in Iowa and Kansas. It uses the hll_if() function to show only flood events.

StormEvents
| where State in ("IOWA", "KANSAS")
| summarize hll_flood = hll_if(Source, EventType == "Flood") by State
| project State, SourcesOfFloodEvents = dcount_hll(hll_flood)

Output

Estimation accuracy

Related content

• Aggregation function types at a glance
• Using hll() and tdigest()
• hll() (aggregation function)
• hll_merge() (aggregation function)

17 - hll_merge() (aggregation function)

Merges HLL results across the group into a single HLL value.

For more information, see the underlying algorithm (HyperLogLog) and estimation accuracy.

Syntax

hll_merge (hll)

Parameters

Returns

The function returns the merged HLL values of hll across the group.

Example

The following example shows HLL results across a group merged into a single HLL value.

StormEvents
| summarize hllRes = hll(DamageProperty) by bin(StartTime,10m)
| summarize hllMerged = hll_merge(hllRes)

Output

The results show only the first five results in the array.

Estimation accuracy

Related content

• Aggregation function types at a glance
• Using hll() and tdigest()
• hll() (aggregation function)
• hll_if() (aggregation function)

18 - hll() (aggregation function)

The hll() function is a way to estimate the number of unique values in a set of values. It does so by calculating intermediate results for aggregation within the summarize operator for a group of data using the dcount function.

Read about the underlying algorithm (HyperLogLog) and the estimation accuracy.

Syntax

hll (expr [, accuracy])

Parameters

Returns

Returns the intermediate results of distinct count of expr across the group.

Example

In the following example, the hll() function is used to estimate the number of unique values of the DamageProperty column within each 10-minute time bin of the StartTime column.

StormEvents
| summarize hll(DamageProperty) by bin(StartTime,10m)

Output

The results table shown includes only the first 10 rows.

Estimation accuracy

Related content

• Aggregation function types at a glance
• Using hll() and tdigest()
• hll_if() (aggregation function)
• hll_merge() (aggregation function)

19 - make_bag_if() (aggregation function)

Creates a dynamic JSON property bag (dictionary) of expr values in records for which predicate evaluates to true.

Syntax

make_bag_if(expr, predicate [, maxSize])

Parameters

Returns

Returns a dynamic JSON property bag (dictionary) of expr values in records for which predicate evaluates to true. Nondictionary values are skipped. If a key appears in more than one row, an arbitrary value, out of the possible values for this key, are selected.

Example

The following example shows a packed JSON property bag.

let T = datatable(prop:string, value:string, predicate:bool)
[
    "prop01", "val_a", true,
    "prop02", "val_b", false,
    "prop03", "val_c", true
];
T
| extend p = bag_pack(prop, value)
| summarize dict=make_bag_if(p, predicate)

Output

Use bag_unpack() plugin for transforming the bag keys in the make_bag_if() output into columns.

let T = datatable(prop:string, value:string, predicate:bool)
[
    "prop01", "val_a", true,
    "prop02", "val_b", false,
    "prop03", "val_c", true
];
T
| extend p = bag_pack(prop, value)
| summarize bag=make_bag_if(p, predicate)
| evaluate bag_unpack(bag)

Output

Related content

• Aggregation function types at a glance
• make_bag() (aggregation function)
• bag_unpack()
• bag_pack()
• make_list_if() (aggregation function)
• parse_json()

20 - make_bag() (aggregation function)

Creates a dynamic JSON property bag (dictionary) of all the values of expr in the group.

Syntax

make_bag (expr [, maxSize])

Parameters

Returns

Returns a dynamic JSON property bag (dictionary) of all the values of Expr in the group, which are property bags. Nondictionary values are skipped. If a key appears in more than one row, an arbitrary value, out of the possible values for this key, is selected.

Example

The following example shows a packed JSON property bag.

let T = datatable(prop:string, value:string)
[
    "prop01", "val_a",
    "prop02", "val_b",
    "prop03", "val_c",
];
T
| extend p = bag_pack(prop, value)
| summarize dict=make_bag(p)

Output

Use the bag_unpack() plugin for transforming the bag keys in the make_bag() output into columns.

let T = datatable(prop:string, value:string)
[
    "prop01", "val_a",
    "prop02", "val_b",
    "prop03", "val_c",
];
T
| extend p = bag_pack(prop, value)
| summarize bag=make_bag(p)
| evaluate bag_unpack(bag)

Output

Related content

• Aggregation function types at a glance
• make_bag_if() (aggregation function)
• bag_unpack()
• bag_pack()
• make_list() (aggregation function)
• parse_json()

21 - make_list_if() (aggregation function)

Creates a dynamic array of expr values in the group for which predicate evaluates to true.

Syntax

make_list_if(expr, predicate [, maxSize])

Parameters

Returns

Returns a dynamic array of expr values in the group for which predicate evaluates to true. If the input to the summarize operator isn’t sorted, the order of elements in the resulting array is undefined. If the input to the summarize operator is sorted, the order of elements in the resulting array tracks that of the input.

Example

The following example shows a list of names with more than 4 letters.

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

Output

Related content

• Aggregation function types at a glance
• make_list
• make_bag_if() (aggregation function)

22 - make_list_with_nulls() (aggregation function)

Creates a dynamic array of all the values of expr in the group, including null values.

Syntax

make_list_with_nulls(expr)

Parameters

Returns

Returns a dynamic JSON object (array) of all the values of expr in the group, including null values. If the input to the summarize operator isn’t sorted, the order of elements in the resulting array is undefined. If the input to the summarize operator is sorted, the order of elements in the resulting array tracks that of the input.

Example

The following example shows null values in the results.

let shapes = datatable (name:string , sideCount: int)
[
    "triangle", int(null),
    "square", 4,
    "rectangle", 4,
    "pentagon", 5,
    "hexagon", 6,
    "heptagon", 7,
    "octagon", 8,
    "nonagon", 9,
    "decagon", 10
];
shapes
| summarize mylist = make_list_with_nulls(sideCount)

Output

23 - make_list() (aggregation function)

Creates a dynamic array of all the values of expr in the group.

Syntax

make_list(expr [, maxSize])

Parameters

Returns

Returns a dynamic array of all the values of expr in the group. If the input to the summarize operator isn’t sorted, the order of elements in the resulting array is undefined. If the input to the summarize operator is sorted, the order of elements in the resulting array tracks that of the input.

Examples

The examples in this section show how to use the syntax to help you get started.

One column

The following example uses the datatable, shapes, to return a list of shapes in a single column.

let shapes = datatable (name: string, sideCount: int)
[
    "triangle", 3,
    "square", 4,
    "rectangle", 4,
    "pentagon", 5,
    "hexagon", 6,
    "heptagon", 7,
    "octagon", 8,
    "nonagon", 9,
    "decagon", 10
];
shapes
| summarize mylist = make_list(name)

Output

Using the ‘by’ clause

The following example uses the make_list function and the by clause to create two lists of objects grouped by whether they have an even or odd number of sides.

let shapes = datatable (name: string, sideCount: int)
[
    "triangle", 3,
    "square", 4,
    "rectangle", 4,
    "pentagon", 5,
    "hexagon", 6,
    "heptagon", 7,
    "octagon", 8,
    "nonagon", 9,
    "decagon", 10
];
shapes
| summarize mylist = make_list(name) by isEvenSideCount = sideCount % 2 == 0

Output

Packing a dynamic object

The following examples show how to pack a dynamic object in a column before making it a list. It returns a column with a boolean table isEvenSideCount indicating whether the side count is even or odd and a mylist column that contains lists of packed bags int each category.

let shapes = datatable (name: string, sideCount: int)
[
    "triangle", 3,
    "square", 4,
    "rectangle", 4,
    "pentagon", 5,
    "hexagon", 6,
    "heptagon", 7,
    "octagon", 8,
    "nonagon", 9,
    "decagon", 10
];
shapes
| extend d = bag_pack("name", name, "sideCount", sideCount)
| summarize mylist = make_list(d) by isEvenSideCount = sideCount % 2 == 0

Output

Related content

• Aggregation function types at a glance
• make_list_if
• make_bag() (aggregation function)

24 - make_set_if() (aggregation function)

Creates a dynamic array of the set of distinct values that expr takes in records for which predicate evaluates to true.

Syntax

make_set_if(expr, predicate [, maxSize])

Parameters

Returns

Returns a dynamic array of the set of distinct values that expr takes in records for which predicate evaluates to true. The array’s sort order is undefined.

Example

The following example shows a list of names with more than four letters.

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

Output

Related content

• Aggregation function types at a glance
• make_set() (aggregation function)
• make_list_if() (aggregation function)
• make_bag_if() (aggregation function)
• mv-expand

25 - make_set() (aggregation function)

Creates a dynamic array of the set of distinct values that expr takes in the group.

Syntax

make_set(expr [, maxSize])

Parameters

Returns

Returns a dynamic array of the set of distinct values that expr takes in the group. The array’s sort order is undefined.

Example

Set from a scalar column

The following example shows the set of states grouped with the same amount of crop damage.

StormEvents 
| summarize states=make_set(State) by DamageCrops

The results table shown includes only the first 10 rows.

Set from array column

The following example shows the set of elements in an array.

datatable (Val: int, Arr1: dynamic)
[
    1, dynamic(['A1', 'A2', 'A3']), 
    5, dynamic(['A2', 'C1']),
    7, dynamic(['C2', 'A3']),
    5, dynamic(['C2', 'A1'])
] 
| summarize Val_set=make_set(Val), Arr1_set=make_set(Arr1)

Related content

• Aggregation function types at a glance
• make_set_if
• make_list
• make_bag() (aggregation function)
• mv-expand

26 - max() (aggregation function)

Finds the maximum value of the expression in the table.

Syntax

max(expr)

Parameters

Returns

Returns the value in the table that maximizes the specified expression.

Example

The following example returns the last record in a table by querying the maximum value for StartTime.

StormEvents
| summarize LatestEvent=max(StartTime)

Output

Related content

• Aggregation function types at a glance
• arg_max
• min function
• avg function
• percentile function

27 - maxif() (aggregation function)

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

See also - max() function, which returns the maximum value across the group without predicate expression.

Syntax

maxif(expr,predicate)

Parameters

Returns

Returns the maximum value of expr in records for which predicate evaluates to true.

Example

This example shows the maximum damage for events with no casualties.

StormEvents
| extend Damage=DamageCrops + DamageProperty, Deaths=DeathsDirect + DeathsIndirect
| summarize MaxDamageNoCasualties=maxif(Damage, Deaths == 0) by State

Output

The results table shown includes only the first 10 rows.

Related content

• Aggregation function types at a glance
• minif() (aggregation function)
• max_of()
• arg_max() (aggregation function)

28 - min() (aggregation function)

Finds the minimum value of the expression in the table.

Syntax

min (expr)

Parameters

Returns

Returns the minimum value of expr across the table.

Example

This example returns the first record in a table.

StormEvents
| summarize FirstEvent=min(StartTime)

Output

Related content

• arg_min function
• max function
• avg function
• percentile function
• min-of function

29 - minif() (aggregation function)

Returns the minimum of Expr in records for which Predicate evaluates to true.

• Can be used only in context of aggregation inside summarize

See also - min() function, which returns the minimum value across the group without predicate expression.

Syntax

minif (Expr,Predicate)

Parameters

Returns

The minimum value of Expr in records for which Predicate evaluates to true.

Example

This example shows the minimum damage for events with casualties (Except 0)

StormEvents
| extend Damage=DamageCrops+DamageProperty, Deaths=DeathsDirect+DeathsIndirect
| summarize MinDamageWithCasualties=minif(Damage,(Deaths >0) and (Damage >0)) by State 
| where MinDamageWithCasualties >0 and isnotnull(MinDamageWithCasualties)

Output

The results table shown includes only the first 10 rows.

Related content

• Aggregation function types at a glance
• maxif() (aggregation function)
• min_of()
• arg_max() (aggregation function)

30 - percentile(), percentiles()

The percentile() function calculates an estimate for the specified nearest-rank percentile of the population defined by expr. The accuracy depends on the density of population in the region of the percentile.

percentiles() works similarly to percentile(). However, percentiles() can calculate multiple percentile values at once, which is more efficient than calculating each percentile value separately.

To calculate weighted percentiles, see percentilesw().

Syntax

percentile(expr, percentile)

percentiles(expr, percentiles)

Parameters

Returns

Returns a table with the estimates for expr of the specified percentiles in the group, each in a separate column.

Examples

Calculate single percentile

The following example shows the value of DamageProperty being larger than 95% of the sample set and smaller than 5% of the sample set.

StormEvents | summarize percentile(DamageProperty, 95) by State

Output

The results table shown includes only the first 10 rows.

Calculate multiple percentiles

The following example shows the value of DamageProperty simultaneously calculated using 5, 50 (median) and 95.

StormEvents | summarize percentiles(DamageProperty, 5, 50, 95) by State

Output

The results table shown includes only the first 10 rows.

Return percentiles as an array

Instead of returning the values in individual columns, use the percentiles_array() function to return the percentiles in a single column of dynamic array type.

Syntax

percentiles_array(expr, percentiles)

Parameters

Returns

Returns an estimate for expr of the specified percentiles in the group as a single column of dynamic array type.

Examples

Comma-separated percentiles

Multiple percentiles can be obtained as an array in a single dynamic column, instead of in multiple columns as with percentiles().

TransformedSensorsData
| summarize percentiles_array(Value, 5, 25, 50, 75, 95), avg(Value) by SensorName

Output

The results table displays only the first 10 rows.

Dynamic array of percentiles

Percentiles for percentiles_array can be specified in a dynamic array of integer or floating-point numbers. The array must be constant but doesn’t have to be literal.

TransformedSensorsData
| summarize percentiles_array(Value, dynamic([5, 25, 50, 75, 95])), avg(Value) by SensorName

Output

The results table displays only the first 10 rows.

Nearest-rank percentile

P-th percentile (0 < P <= 100) of a list of ordered values, sorted in ascending order, is the smallest value in the list. The P percent of the data is less or equal to P-th percentile value (from Wikipedia article on percentiles).

Define 0-th percentiles to be the smallest member of the population.

Estimation error in percentiles

The percentiles aggregate provides an approximate value using T-Digest.

Related content

• Aggregation function types at a glance
• percentilew(), percentilesw() (aggregation function)
• avg function

31 - percentilew(), percentilesw()

The percentilew() function calculates a weighted estimate for the specified nearest-rank percentile of the population defined by expr. percentilesw() works similarly to percentilew(). However, percentilesw() can calculate multiple weighted percentile values at once, which is more efficient than calculating each weighted percentile value separately.

Weighted percentiles calculate percentiles in a dataset by giving each value in the input dataset a weight. In this method, each value is considered to be repeated a number of times equal to its weight, which is then used to calculate the percentile. By giving more importance to certain values, weighted percentiles provide a way to calculate percentiles in a “weighted” manner.

To calculate unweighted percentiles, see percentiles().

Syntax

percentilew(expr, weightExpr, percentile)

percentilesw(expr, weightExpr, percentiles)

Parameters

Returns

Returns a table with the estimates for expr of the specified percentiles in the group, each in a separate column.

Examples

Calculate weighted percentiles

Assume you repetitively measure the time (Duration) it takes an action to complete. Instead of recording every value of the measurement, you record each value of Duration, rounded to 100 msec, and how many times the rounded value appeared (BucketSize).

Use summarize percentilesw(Duration, BucketSize, ...) to calculate the given percentiles in a “weighted” way. Treat each value of Duration as if it was repeated BucketSize times in the input, without actually needing to materialize those records.

The following example shows weighted percentiles. Using the following set of latency values in milliseconds: { 1, 1, 2, 2, 2, 5, 7, 7, 12, 12, 15, 15, 15, 18, 21, 22, 26, 35 }.

To reduce bandwidth and storage, do pre-aggregation to the following buckets: { 10, 20, 30, 40, 50, 100 }. Count the number of events in each bucket to produce the following table:

let latencyTable = datatable (ReqCount:long, LatencyBucket:long) 
[ 
    8, 10, 
    6, 20, 
    3, 30, 
    1, 40 
];
latencyTable

The table displays:

• Eight events in the 10-ms bucket (corresponding to subset { 1, 1, 2, 2, 2, 5, 7, 7 })
• Six events in the 20-ms bucket (corresponding to subset { 12, 12, 15, 15, 15, 18 })
• Three events in the 30-ms bucket (corresponding to subset { 21, 22, 26 })
• One event in the 40-ms bucket (corresponding to subset { 35 })

At this point, the original data is no longer available. Only the number of events in each bucket. To compute percentiles from this data, use the percentilesw() function. For the 50, 75, and 99.9 percentiles, use the following query:

let latencyTable = datatable (ReqCount:long, LatencyBucket:long) 
[ 
    8, 10, 
    6, 20, 
    3, 30, 
    1, 40 
];
latencyTable
| summarize percentilesw(LatencyBucket, ReqCount, 50, 75, 99.9)

Output

Return percentiles as an array

Instead of returning the values in individual columns, use the percentilesw_array() function to return the percentiles in a single column of dynamic array type.

Syntax

percentilesw_array(expr, weightExpr, percentiles)

Parameters

Returns

Returns an estimate for expr of the specified percentiles in the group as a single column of dynamic array type.

Examples

Comma-separated percentiles

let latencyTable = datatable (ReqCount:long, LatencyBucket:long) 
[ 
    8, 10, 
    6, 20, 
    3, 30, 
    1, 40 
];
latencyTable
| summarize percentilesw_array(LatencyBucket, ReqCount, 50, 75, 99.9)

Output

Dynamic array of percentiles

let latencyTable = datatable (ReqCount:long, LatencyBucket:long) 
[ 
    8, 10, 
    6, 20, 
    3, 30, 
    1, 40 
];
latencyTable
| summarize percentilesw_array(LatencyBucket, ReqCount, dynamic([50, 75, 99.9]))

Output

Related content

• Aggregation function types at a glance
• percentile(), percentiles() (aggregation function)
• avg function

32 - stdev() (aggregation function)

Calculates the standard deviation of expr across the group, using Bessel’s correction for a small dataset that is considered a sample.

For a large dataset that is representative of the population, use stdevp() (aggregation function).

Formula

This function uses the following formula.

Syntax

stdev(expr)

Parameters

Returns

Returns the standard deviation value of expr across the group.

Example

The following example shows the standard deviation for the group.

range x from 1 to 5 step 1
| summarize make_list(x), stdev(x)

Output

33 - stdevif() (aggregation function)

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

Syntax

stdevif(expr,predicate)

Parameters

Returns

Returns the standard deviation value of expr in records for which predicate evaluates to true.

Example

The following example shows the standard deviation in a range of 1 to 100.

range x from 1 to 100 step 1
| summarize stdevif(x, x % 2 == 0)

Output

34 - stdevp() (aggregation function)

Calculates the standard deviation of expr across the group, considering the group as a population for a large dataset that is representative of the population.

For a small dataset that is a sample, use stdev() (aggregation function).

Formula

This function uses the following formula.

Syntax

stdevp(expr)

Parameters

Returns

Returns the standard deviation value of expr across the group.

Example

range x from 1 to 5 step 1
| summarize make_list(x), stdevp(x)

Output

35 - sum() (aggregation function)

Calculates the sum of expr across the group.

Syntax

sum(expr)

Parameters

Returns

Returns the sum value of expr across the group.

Example

This example returns the total value of crop and property damages by state, and sorted in descending value.

StormEvents 
| summarize EventCount=count(), TotalDamages = sum(DamageCrops+DamageProperty) by State 
| sort by TotalDamages

Output

The results table shown includes only the first 10 rows.

| State | Eventcount | TotalDamages | | —- | — | | CALIFORNIA | 898 | 2801954600 | | GEORGIA | 1983 | 1190448750 | | MISSOURI | 2016 | 1096887450 | | OKLAHOMA | 1716 | 916557300 | | MISSISSIPPI | 1218 | 802890160 | | KANSAS | 3166 | 738830000 | | TEXAS | 4701 | 572086700 | | OHIO | 1233 | 417989500 | | FLORIDA | 1042 | 379455260 | | NORTH DAKOTA | 905 | 342460100 | | … | … | … |

36 - sumif() (aggregation function)

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

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

Syntax

sumif(expr,predicate)

Parameters

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.

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

Output

The results table shown includes only the first 10 rows.

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

37 - take_any() (aggregation function)

Arbitrarily chooses one record for each group in a summarize operator, and returns the value of one or more expressions over each such record.

Syntax

take_any(expr_1 [, expr_2 …])

take_any(*)

Parameters

Returns

The take_any aggregation function returns the values of the expressions calculated for each of the records selected Indeterministically from each group of the summarize operator.

If the * argument is provided, the function behaves as if the expressions are all columns of the input to the summarize operator barring the group-by columns, if any.

Remarks

This function is useful when you want to get a sample value of one or more columns per value of the compound group key.

When the function is provided with a single column reference, it will attempt to return a non-null/non-empty value, if such value is present.

As a result of the indeterministic nature of this function, using this function multiple times in a single application of the summarize operator isn’t equivalent to using this function a single time with multiple expressions. The former may have each application select a different record, while the latter guarantees that all values are calculated over a single record (per distinct group).

Examples

Show indeterministic State:

StormEvents
| summarize take_any(State)

Output

Show all the details for a random record:

StormEvents
| project StartTime, EpisodeId, State, EventType
| summarize take_any(*)

Output

Show all the details of a random record for each State starting with ‘A’:

StormEvents
| where State startswith "A"
| project StartTime, EpisodeId, State, EventType
| summarize take_any(*) by State

Output

Related content

• take_anyif function
• arg_max function
• arg_min function

38 - take_anyif() (aggregation function)

Arbitrarily selects one record for each group in a summarize operator in records for which the predicate is ’true’. The function returns the value of an expression over each such record.

This function is useful when you want to get a sample value of one column per value of the compound group key, subject to some predicate that is true. If such a value is present, the function attempts to return a non-null/non-empty value.

Syntax

take_anyif( expr, predicate )

Parameters

Returns

The take_anyif aggregation function returns the value of the expression calculated for each of the records randomly selected from each group of the summarize operator. Only records for which predicate returns ’true’ may be selected. If the predicate doesn’t return ’true’, a null value is produced.

Examples

Pick a random EventType from Storm events, where event description has a key phrase.

StormEvents
| summarize take_anyif(EventType, EventNarrative has 'strong wind')

Output

Related content

• take_any function
• arg_max function
• arg_min function

39 - tdigest_merge() (aggregation functions)

Merges tdigest results across the group.

For more information about the underlying algorithm (T-Digest) and the estimated error, see estimation error in percentiles.

Syntax

tdigest_merge(expr)

Parameters

Returns

Returns the merged tdigest values of expr across the group.

Example

StormEvents
| summarize PreAggDamageProperty=tdigest(DamageProperty) by State
| summarize tdigest_merge(PreAggDamageProperty)

Output

40 - tdigest() (aggregation function)

Calculates the intermediate results of percentiles() across the group.

For more information, see the underlying algorithm (T-Digest) and the estimated error.

Syntax

tdigest(expr [, weight])

Parameters

Returns

The Intermediate results of weighted percentiles of *expr* across the group.

Examples

Results per state

This example shows the results of the tdigest percentiles sorted by state.

StormEvents
| summarize tdigest(DamageProperty) by State

The results table shown includes only the first 10 rows.

Convert pre-existing centroids

The following example shows how one can convert pre-existing T-Digest centroids for long-term storage. The V column holds the value of each centroid, and the W column is its weight (relative count). The tdigest() aggregate function is then applied to convert the data in table DT into the internal representation, and percentile_tdigest() is used to demonstrate how ot find the 50-tile value.

let DT=datatable(V:real, W:long) [
    1.0, 1,
    2.0, 2
];
DT
| summarize TD=tdigest(V, W)
| project P50=percentile_tdigest(TD, 50)

41 - variance() (aggregation function)

Calculates the variance of expr across the group, considering the group as a sample.

The following formula is used:

Syntax

variance(expr)

Parameters

Returns

Returns the variance value of expr across the group.

Example

range x from 1 to 5 step 1
| summarize make_list(x), variance(x) 

Output

42 - varianceif() (aggregation function)

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

Syntax

varianceif(expr, predicate)

Parameters

Returns

Returns the variance value of expr in records for which predicate evaluates to true.

Example

range x from 1 to 100 step 1
| summarize varianceif(x, x%2 == 0)

Output

43 - variancep() (aggregation function)

Calculates the variance of expr across the group, considering the group as a population.

The following formula is used:

Syntax

variancep(expr)

Parameters

Returns

Returns the variance value of expr across the group.

Example

range x from 1 to 5 step 1
| summarize make_list(x), variancep(x) 

Output