Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Query Expression Syntax Examples: Aggregate Operators

The examples in this topic demonstrate how to use the Average, Count, Max, Min, and Sum methods to query the AdventureWorks Sales Model using query expression syntax. The AdventureWorks Sales Model used in these examples is built from the Contact, Address, Product, SalesOrderHeader, and SalesOrderDetail tables in the AdventureWorks sample database.

The examples in this topic use the following using/Imports statements:


using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using System.Globalization;
using System.Data.EntityClient;
using System.Data.SqlClient;
using System.Data.Common;
    

Average

Example

The following example uses the Average method to find the average list price of the products of each style.


using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<Product> products = context.Products;

var query = from product in products
group product by product.Style into g
select new
{
Style = g.Key,
AverageListPrice =
g.Average(product => product.ListPrice)
};

foreach (var product in query)
{
Console.WriteLine("Product style: {0} Average list price: {1}",
product.Style, product.AverageListPrice);
}
}
    

Example

The following example uses Average to get the average total due for each contact ID.


using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

var query =
from order in orders
group order by order.Contact.ContactID into g
select new
{
Category = g.Key,
averageTotalDue = g.Average(order => order.TotalDue)
};

foreach (var order in query)
{
Console.WriteLine("ContactID = {0} \t Average TotalDue = {1}",
order.Category, order.averageTotalDue);
}
}
    

Example

The following example uses Average to get the orders with the average total due for each contact.


using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

var query =
from order in orders
group order by order.Contact.ContactID into g
let averageTotalDue = g.Average(order => order.TotalDue)
select new
{
Category = g.Key,
CheapestProducts =
g.Where(order => order.TotalDue == averageTotalDue)
};

foreach (var orderGroup in query)
{
Console.WriteLine("ContactID: {0}", orderGroup.Category);
foreach (var order in orderGroup.CheapestProducts)
{
Console.WriteLine("Average total due for SalesOrderID {1} is: {0}",
order.TotalDue, order.SalesOrderID);
}
Console.Write("\n");
}
}
    

Count

Example

The following example uses Count to return a list of contact IDs and how many orders each has.


using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<Contact> contacts = context.Contacts;

//Can't find field SalesOrderContact
var query =
from contact in contacts
select new
{
CustomerID = contact.ContactID,
OrderCount = contact.SalesOrderHeaders.Count()
};

foreach (var contact in query)
{
Console.WriteLine("CustomerID = {0} \t OrderCount = {1}",
contact.CustomerID,
contact.OrderCount);
}
}
    

Example

The following example groups products by color and uses Count to return the number of products in each color group.


using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<Product> products = context.Products;

var query =
from product in products
group product by product.Color into g
select new { Color = g.Key, ProductCount = g.Count() };

foreach (var product in query)
{
Console.WriteLine("Color = {0} \t ProductCount = {1}",
product.Color,
product.ProductCount);
}
}
    

Max

Example

The following example uses the Max method to get the largest total due for each contact ID.


using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

var query =
from order in orders
group order by order.Contact.ContactID into g
select new
{
Category = g.Key,
maxTotalDue =
g.Max(order => order.TotalDue)
};

foreach (var order in query)
{
Console.WriteLine("ContactID = {0} \t Maximum TotalDue = {1}",
order.Category, order.maxTotalDue);
}
}
    

Example

The following example uses the Max method to get the orders with the largest total due for each contact ID.


using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

var query =
from order in orders
group order by order.Contact.ContactID into g
let maxTotalDue = g.Max(order => order.TotalDue)
select new
{
Category = g.Key,
CheapestProducts =
g.Where(order => order.TotalDue == maxTotalDue)
};

foreach (var orderGroup in query)
{
Console.WriteLine("ContactID: {0}", orderGroup.Category);
foreach (var order in orderGroup.CheapestProducts)
{
Console.WriteLine("MaxTotalDue {0} for SalesOrderID {1}: ",
order.TotalDue,
order.SalesOrderID);
}
Console.Write("\n");
}
}
    

Min

Example

The following example uses the Min method to get the smallest total due for each contact ID.


using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

var query =
from order in orders
group order by order.Contact.ContactID into g
select new
{
Category = g.Key,
smallestTotalDue =
g.Min(order => order.TotalDue)
};

foreach (var order in query)
{
Console.WriteLine("ContactID = {0} \t Minimum TotalDue = {1}",
order.Category, order.smallestTotalDue);
}
}
    

Example

The following example uses the Min method to get the orders with the smallest total due for each contact.


using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

var query =
from order in orders
group order by order.Contact.ContactID into g
let minTotalDue = g.Min(order => order.TotalDue)
select new
{
Category = g.Key,
smallestTotalDue =
g.Where(order => order.TotalDue == minTotalDue)
};


foreach (var orderGroup in query)
{
Console.WriteLine("ContactID: {0}", orderGroup.Category);
foreach (var order in orderGroup.smallestTotalDue)
{
Console.WriteLine("Mininum TotalDue {0} for SalesOrderID {1}: ",
order.TotalDue,
order.SalesOrderID);
}
Console.Write("\n");
}
}
    

Sum

Example

The following example uses the Sum method to get the total due for each contact ID.


using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

var query =
from order in orders
group order by order.Contact.ContactID into g
select new
{
Category = g.Key,
TotalDue = g.Sum(order => order.TotalDue)
};

foreach (var order in query)
{
Console.WriteLine("ContactID = {0} \t TotalDue sum = {1}",
order.Category, order.TotalDue);
}
}
    

See Also




Build Date:

2012-10-01
Show:
© 2015 Microsoft