Sideway
output.to from Sideway
Draft for Information Only

Content

Aggregate Clause
 Syntax
 Parts
 Remarks
 Aggregate Functions
  All
  Any
  Average
  Count
  Group
  LongCount
  Max
  Min
  Sum
 Example
 Creating User-Defined Aggregate Functions
 See also
  Source/Reference

Aggregate Clause

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

TermDefinition elementRequired. Variable used to iterate through the elements of the collection. typeOptional. The type of element. If no type is specified, the type of element is inferred from collection. collectionRequired. Refers to the collection to operate on. clauseOptional. One or more query clauses, such as a Where clause, to refine the query result to apply the aggregate clause or clauses to. expressionListRequired. 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

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

See also

Source/Reference


©sideway

ID: 210500009 Last Updated: 5/9/2021 Revision: 0 Ref:

close

References

  1. Active Server Pages,  , http://msdn.microsoft.com/en-us/library/aa286483.aspx
  2. ASP Overview,  , http://msdn.microsoft.com/en-us/library/ms524929%28v=vs.90%29.aspx
  3. ASP Best Practices,  , http://technet.microsoft.com/en-us/library/cc939157.aspx
  4. ASP Built-in Objects,  , http://msdn.microsoft.com/en-us/library/ie/ms524716(v=vs.90).aspx
  5. Response Object,  , http://msdn.microsoft.com/en-us/library/ms525405(v=vs.90).aspx
  6. Request Object,  , http://msdn.microsoft.com/en-us/library/ms524948(v=vs.90).aspx
  7. Server Object (IIS),  , http://msdn.microsoft.com/en-us/library/ms525541(v=vs.90).aspx
  8. Application Object (IIS),  , http://msdn.microsoft.com/en-us/library/ms525360(v=vs.90).aspx
  9. Session Object (IIS),  , http://msdn.microsoft.com/en-us/library/ms524319(8v=vs.90).aspx
  10. ASPError Object,  , http://msdn.microsoft.com/en-us/library/ms524942(v=vs.90).aspx
  11. ObjectContext Object (IIS),  , http://msdn.microsoft.com/en-us/library/ms525667(v=vs.90).aspx
  12. Debugging Global.asa Files,  , http://msdn.microsoft.com/en-us/library/aa291249(v=vs.71).aspx
  13. How to: Debug Global.asa files,  , http://msdn.microsoft.com/en-us/library/ms241868(v=vs.80).aspx
  14. Calling COM Components from ASP Pages,  , http://msdn.microsoft.com/en-us/library/ms524620(v=VS.90).aspx
  15. IIS ASP Scripting Reference,  , http://msdn.microsoft.com/en-us/library/ms524664(v=vs.90).aspx
  16. ASP Keywords,  , http://msdn.microsoft.com/en-us/library/ms524672(v=vs.90).aspx
  17. Creating Simple ASP Pages,  , http://msdn.microsoft.com/en-us/library/ms524741(v=vs.90).aspx
  18. Including Files in ASP Applications,  , http://msdn.microsoft.com/en-us/library/ms524876(v=vs.90).aspx
  19. ASP Overview,  , http://msdn.microsoft.com/en-us/library/ms524929(v=vs.90).aspx
  20. FileSystemObject Object,  , http://msdn.microsoft.com/en-us/library/z9ty6h50(v=vs.84).aspx
  21. http://msdn.microsoft.com/en-us/library/windows/desktop/ms675944(v=vs.85).aspx,  , ADO Object Model
  22. ADO Fundamentals,  , http://msdn.microsoft.com/en-us/library/windows/desktop/ms680928(v=vs.85).aspx
close

Latest Updated LinksValid XHTML 1.0 Transitional Valid CSS!Nu Html Checker Firefox53 Chromena IExplorerna
IMAGE

Home 5

Business

Management

HBR 3

Information

Recreation

Hobbies 8

Culture

Chinese 1097

English 339

Reference 79

Computer

Hardware 249

Software

Application 213

Digitization 32

Latex 52

Manim 205

KB 1

Numeric 19

Programming

Web 289

Unicode 504

HTML 66

CSS 65

SVG 46

ASP.NET 270

OS 429

DeskTop 7

Python 72

Knowledge

Mathematics

Formulas 8

Algebra 84

Number Theory 206

Trigonometry 31

Geometry 34

Coordinate Geometry 2

Calculus 67

Complex Analysis 21

Engineering

Tables 8

Mechanical

Mechanics 1

Rigid Bodies

Statics 92

Dynamics 37

Fluid 5

Fluid Kinematics 5

Control

Process Control 1

Acoustics 19

FiniteElement 2

Natural Sciences

Matter 1

Electric 27

Biology 1

Geography 1


Copyright © 2000-2024 Sideway . All rights reserved Disclaimers last modified on 06 September 2019