Aggregation operators are Average, Count, LongCount, Sum, Max, Min and Aggregate. Aggregation operators are available only as methods in C# (VB also have a comprehension syntax for aggregation operators).
Average: Gets the average of a numeric collection. T-SQL counterpart is Avg()
Sum: Sums the values in a numeric collection. T-SQL counterpart is Sum()
Min,Max: Gets minimum and largest values in a sequence. T-SQL counterparts are Min(), Max()
Count, LongCount: Counts the elements in a collection. T-SQL counterparts are Count(), Count_Big().
Aggregate: Performs custom aggregation on a set of values. T-SQL has no counterpart.
Count and LongCount have a filtering parameter that filters what to count based on a boolean expression as in this sample:
Count and LongCount are same except that LongCount returns a long (64 bits integer).
All these methods (except Count and LongCount) have a "selector" parameter to specify "what" to sum,average,min or max. Here are some samples:
This sample uses Min, Max, Count, Sum to generate a summary statistics on customer purchases. Here is a sample for getting averages:
Aggregate is the interesting one here that is new to VFP developers (and SQL developers as well). Aggregate allows custom aggregations to be done and is supported only in local data (not supported by Linq To SQL). Here are some samples:
First one is simply a custom implementation of Sum(). Second one gets a product of the numbers in list ( 1 * 2 * 3 * 4 … 10 ). Next example shows that to use aggregate it doesn’t have to be a numeric.
This sample creates a comma separated list of all customer ID values. Note that we get the customer IDs to an array first to make the collection local.
Aggregate have two more signatures. Next one uses an initial "seed" value (in the above samples the seed value was the first element in the list). I think VS Linq samples have one of the best samples for this one:
Next version of Aggregate allows you to have a custom selector projection on the result.
It looks confusing and yes it is. Probably you wouldn’t want to aggregate like this but use a for loop. I wrote this one just to show how you could initialize a complex (here an anonymous type with Beverages,Condiments and Other properties) seed value, do accumulation based on category names and finally project the result to a custom string.