# Aggregate Clause (Visual Basic)

Applies one or more aggregate functions to a collection.

## Syntax

```
Aggregate element [As type] In collection _
[, element2 [As type2] In collection2, [...]]
[ clause ]
Into expressionList
```

## Parts

Term | Definition |
---|---|

`element` |
Required. Variable used to iterate through the elements of the collection. |

`type` |
Optional. The type of `element` . If no type is specified, the type of `element` is inferred from `collection` . |

`collection` |
Required. Refers to the collection to operate on. |

`clause` |
Optional. One or more query clauses, such as a `Where` clause, to refine the query result to apply the aggregate clause or clauses to. |

`expressionList` |
Required. One or more comma-delimited expressions that identify an aggregate function to apply to the collection. You can apply an alias to an aggregate function to specify a member name for the query result. If no alias is supplied, the name of the aggregate function is used. For examples, see the section about aggregate functions later in this topic. |

## Remarks

The `Aggregate`

clause can be used to include aggregate functions in your queries. Aggregate functions perform checks and computations over a set of values and return a single value. You can access the computed value by using a member of the query result type. The standard aggregate functions that you can use are the `All`

, `Any`

, `Average`

, `Count`

, `LongCount`

, `Max`

, `Min`

, and `Sum`

functions. These functions are familiar to developers who are familiar with aggregates in SQL. They are described in the following section of this topic.

The result of an aggregate function is included in the query result as a field of the query result type. You can supply an alias for the aggregate function result to specify the name of the member of the query result type that will hold the aggregate value. If no alias is supplied, the name of the aggregate function is used.

The `Aggregate`

clause can begin a query, or it can be included as an additional clause in a query. If the `Aggregate`

clause begins a query, the result is a single value that is the result of the aggregate function specified in the `Into`

clause. If more than one aggregate function is specified in the `Into`

clause, the query returns a single type with a separate property to reference the result of each aggregate function in the `Into`

clause. If the `Aggregate`

clause is included as an additional clause in a query, the type returned in the query collection will have a separate property to reference the result of each aggregate function in the `Into`

clause.

## Aggregate Functions

The following are the standard aggregate functions that can be used with the `Aggregate`

clause.

### All

Returns `true`

if all elements in the collection satisfy a specified condition; otherwise returns `false`

. The following is an example:

```
Dim customerList1 = Aggregate order In orders
Into AllOrdersOver100 = All(order.Total >= 100)
```

### Any

Returns `true`

if any element in the collection satisfies a specified condition; otherwise returns `false`

. The following is an example:

```
Dim customerList2 = From cust In customers
Aggregate order In cust.Orders
Into AnyOrderOver500 = Any(order.Total >= 500)
```

### Average

Computes the average of all elements in the collection, or computes a supplied expression for all elements in the collection. The following is an example:

```
Dim customerOrderAverage = Aggregate order In orders
Into Average(order.Total)
```

### Count

Counts the number of elements in the collection. You can supply an optional `Boolean`

expression to count only the number of elements in the collection that satisfy a condition. The following is an example:

```
Dim customerOrderAfter1996 = From cust In customers
Aggregate order In cust.Orders
Into Count(order.OrderDate > #12/31/1996#)
```

### Group

Refers to query results that are grouped as a result of a `Group By`

or `Group Join`

clause. The `Group`

function is valid only in the `Into`

clause of a `Group By`

or `Group Join`

clause. For more information and examples, see Group By Clause and Group Join Clause.

### LongCount

Counts the number of elements in the collection. You can supply an optional `Boolean`

expression to count only the number of elements in the collection that satisfy a condition. Returns the result as a `Long`

. For an example, see the `Count`

aggregate function.

### Max

Computes the maximum value from the collection, or computes a supplied expression for all elements in the collection. The following is an example:

```
Dim customerMaxOrder = Aggregate order In orders
Into MaxOrder = Max(order.Total)
```

### Min

Computes the minimum value from the collection, or computes a supplied expression for all elements in the collection. The following is an example:

```
Dim customerMinOrder = From cust In customers
Aggregate order In cust.Orders
Into MinOrder = Min(order.Total)
```

### Sum

Computes the sum of all elements in the collection, or computes a supplied expression for all elements in the collection. The following is an example:

```
Dim customerTotals = From cust In customers
Aggregate order In cust.Orders
Into Sum(order.Total)
```

## Example

The following example shows how to use the `Aggregate`

clause to apply aggregate functions to a query result.

```
Public Sub AggregateSample()
Dim customers = GetCustomerList()
Dim customerOrderTotal =
From cust In customers
Aggregate order In cust.Orders
Into Sum(order.Total), MaxOrder = Max(order.Total),
MinOrder = Min(order.Total), Avg = Average(order.Total)
For Each customer In customerOrderTotal
Console.WriteLine(customer.cust.CompanyName & vbCrLf &
vbTab & "Sum = " & customer.Sum & vbCrLf &
vbTab & "Min = " & customer.MinOrder & vbCrLf &
vbTab & "Max = " & customer.MaxOrder & vbCrLf &
vbTab & "Avg = " & customer.Avg.ToString("#.##"))
Next
End Sub
```

## Creating User-Defined Aggregate Functions

You can include your own custom aggregate functions in a query expression by adding extension methods to the IEnumerable<T> type. Your custom method can then perform a calculation or operation on the enumerable collection that has referenced your aggregate function. For more information about extension methods, see Extension Methods.

For example, the following example shows a custom aggregate function that calculates the median value of a collection of numbers. There are two overloads of the `Median`

extension method. The first overload accepts, as input, a collection of type `IEnumerable(Of Double)`

. If the `Median`

aggregate function is called for a query field of type `Double`

, this method will be called. The second overload of the `Median`

method can be passed any generic type. The generic overload of the `Median`

method takes a second parameter that references the `Func(Of T, Double)`

lambda expression to project a value for a type (from a collection) as the corresponding value of type `Double`

. It then delegates the calculation of the median value to the other overload of the `Median`

method. For more information about lambda expressions, see Lambda Expressions.

```
Imports System.Runtime.CompilerServices
Module UserDefinedAggregates
' Calculate the median value for a collection of type Double.
<Extension()>
Function Median(ByVal values As IEnumerable(Of Double)) As Double
If values.Count = 0 Then
Throw New InvalidOperationException("Cannot compute median for an empty set.")
End If
Dim sortedList = From number In values
Order By number
Dim medianValue As Double
Dim itemIndex = CInt(Int(sortedList.Count / 2))
If sortedList.Count Mod 2 = 0 Then
' Even number of items in list.
medianValue = ((sortedList(itemIndex) + sortedList(itemIndex - 1)) / 2)
Else
' Odd number of items in list.
medianValue = sortedList(itemIndex)
End If
Return medianValue
End Function
' "Cast" the collection of generic items as type Double and call the
' Median() method to calculate the median value.
<Extension()>
Function Median(Of T)(ByVal values As IEnumerable(Of T),
ByVal selector As Func(Of T, Double)) As Double
Return (From element In values Select selector(element)).Median()
End Function
End Module
```

The following example shows sample queries that call the `Median`

aggregate function on a collection of type `Integer`

, and a collection of type `Double`

. The query that calls the `Median`

aggregate function on the collection of type `Double`

calls the overload of the `Median`

method that accepts, as input, a collection of type `Double`

. The query that calls the `Median`

aggregate function on the collection of type `Integer`

calls the generic overload of the `Median`

method.

```
Module Module1
Sub Main()
Dim numbers1 = {1, 2, 3, 4, 5}
Dim query1 = Aggregate num In numbers1 Into Median(num)
Console.WriteLine("Median = " & query1)
Dim numbers2 = {1.9, 2, 8, 4, 5.7, 6, 7.2, 0}
Dim query2 = Aggregate num In numbers2 Into Median()
Console.WriteLine("Median = " & query2)
End Sub
End Module
```