Aggregate Operators

Home	Table Design

Count - Simple

This sample uses Count to get the number of unique customer’s Education.

DataClassesDataContext db = new DataClassesDataContext();

LINQ:

var result = (from c in db.Customers
select new { c.Education}).Distinct().Count();

Lambda:

var result = db.Customers.Select(c => c.Education).Distinct().Count();

Response.Output.WriteLine("Total Distinct Education : {0}<br>", result);

Result:

Total Distinct Education : 4

Count - Conditional

This sample uses Count to get the number of Education where customer has completed his education.

DataClassesDataContext db = new DataClassesDataContext();

LINQ:

var result = (from c in db.Customers
where c.IsStudent == 0
select new { c.Education}).Distinct().Count();

Lambda:

var result = db.Customers.Where(c => c.IsStudent
== 0).Select(c => c.Education).Distinct().Count();
Response.Output.WriteLine("Total Distinct Education : {0}<br>", result);

Result:

Total Distinct Education : 2

Count - Grouped

This sample uses Count to return a list of categories and how many products each has.

DataClassesDataContext db = new DataClassesDataContext();

var result = from oi in db.CustOrdeItems
group oi by oi.CustOID into Group
select new {Group.Key, Group };

foreach (var item in result)
Response.Output.WriteLine("Key : {0} has {1} items.<br>" ,
item.Key, item.Group.Count());

Result

Key : 1 has 3 items.
Key : 2 has 2 items.
Key : 3 has 1 items.

Sum - Simple

This sample uses Sum to get the total of the price in an Order Item.

DataClassesDataContext db = new DataClassesDataContext();

LINQ:

var result = (from oi in db.CustOrdeItems
select oi.Price).Sum();

Lambda:

var result = db.CustOrdeItems.Sum(f => f.Price);

Response.Output.WriteLine("Sum : {0}", result);

Result:

Sum : 2320

Sum - Grouped

This sample uses Sum to get the total amount in order item for each order.

DataClassesDataContext db = new DataClassesDataContext();

var result = from oi in db.CustOrdeItems
group oi by oi.CustOID into Group
select new {Group.Key, TotPrice = Group.Sum(o => o.Price)};

foreach (var item in result)
Response.Output.WriteLine("Order Item:{0} has Total {1}
 Amount orders<br>", item.Key, item.TotPrice);

Result:

Order Item:1 has Total 720 Amount orders
Order Item:2 has Total 800 Amount orders
Order Item:3 has Total 800 Amount orders

Min - Simple

This sample uses Min to get the lowest price in item.

DataClassesDataContext db = new DataClassesDataContext();

var result = db.CustOrdeItems.Min(oi => oi.Price);

Response.Output.WriteLine("Lowest Price: {0} <br>", result);

Result:
Lowest Price: 20

Min - Grouped

This sample uses Min to get the cheapest price among each customer’s order.

DataClassesDataContext db = new DataClassesDataContext();

LINQ:

var result = from oi in db.CustOrdeItems
group oi by oi.CustOID into Group
select new{Group.Key, MinPrice = Group.Min(p => p.Price)};

foreach (var item in result)
Response.Output.WriteLine("Cust Order {0} has {1} Min price<br> "
, item.Key, item.MinPrice);

Result:

Cust Order 1 has 20 Min price
Cust Order 2 has 300 Min price
Cust Order 3 has 800 Min price

Max - Simple

This sample uses Max to get the highest price from orderItem.

LINQ:

DataClassesDataContext db = new DataClassesDataContext();

var result = db.CustOrdeItems.Max(oi => oi.Price);

Response.Output.WriteLine("Highest Price: {0} <br>", result);

Result:

Highest Price: 800

Max - Projection

This sample uses Max to get the length of the longest word in an orderItem.

DataClassesDataContext db = new DataClassesDataContext();

LINQ:

var result = (from oi in db.CustOrdeItems
select oi.Item.Length).Max();
 
Lamda:

var result = db.CustOrdeItems.Max(oi => oi.Item.Length);

Response.Output.WriteLine("Maximum length string: {0} <br>", result);

Result:

Maximum length string: 13

Max - Grouped

This sample uses Max to get the highest price among each customer’s order.

DataClassesDataContext db = new DataClassesDataContext();

var result = from oi in db.CustOrdeItems
group oi by oi.CustOID into Group
select new {Group.Key, MaxPrice = Group.Max(p => p.Price)};

foreach (var item in result)
Response.Output.WriteLine("Customer Order {0} has {1} Max price<br> "
, item.Key, item.MaxPrice);

Result:

Customer Order 1 has 675 Max price
Customer Order 2 has 500 Max price
Customer Order 3 has 800 Max price

Average - Simple

This sample uses Average to get the average of all order items.

DataClassesDataContext db = new DataClassesDataContext();

LINQ:

var result = (from oi in db.CustOrdeItems
select new { oi.Price }).Average(oi => oi.Price);
 
Lambda:

var result = db.CustOrdeItems.Average(oi => oi.Price);
Response.Output.WriteLine("Average : {0} ", result);

Result:
Average : 386.666666666667

Average - Projection

This sample uses Average to get the average length of the item in the array.

DataClassesDataContext db = new DataClassesDataContext();

LINQ:

var result = (from oi in db.CustOrdeItems
select oi.Item.Length).Average();
 
Lambda:

var result = db.CustOrdeItems.Average(oi => oi.Item.Length);

Response.Output.WriteLine("The average of item length is {0}
 characters.<br>", result);
 
Result:

The average of item length is 6 characters.

Average - Grouped

This sample uses Average to get the average price of each category's products.

DataClassesDataContext db = new DataClassesDataContext();

LINQ:

var result = from oi in db.CustOrdeItems
group oi by oi.CustOID into Group
select new { Group.Key, Avg = Group.Average(p => p.Price) };

foreach (var oi in result)
Response.Output.WriteLine( "Order Item {0} has average {1} Amount order.<br>" , oi.Key, oi.Avg);
 
Result:

Order Item 1 has average 240 Amount order.
Order Item 2 has average 400 Amount order.
Order Item 3 has average 800 Amount order.

==============================================
Shradhdha Zalavadiya |Senior Software Engineer

WebMingle Technology
Accelerated by knowledge. Driven by values.
www.webMingle.in