# Aggregate Clause (Visual Basic)

**Visual Studio 2008**

Applies one or more aggregate functions to a collection.

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

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.

The following code 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

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 (Visual Basic).

For example, the following code 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 code 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() As Integer = {1, 2, 3, 4, 5} Dim query1 = Aggregate num In numbers1 Into Median(num) Console.WriteLine("Median = " & query1) Dim numbers2() As Double = {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