Aggregate Canonical Functions
Aggregates are expressions that reduce a series of input values into, for example, a single value. Aggregates are normally used in conjunction with the GROUP BY clause of the SELECT expression, and there are constraints on where they can be used.
Aggregate Entity SQL canonical functions
The following are the aggregate Entity SQL canonical functions.
Avg(expression)
Returns the average of the non-null values.
Arguments
An Int32, Int64, Double, and Decimal.
Return Value
The type of expression, or null if all input values are null values.
Example
queryString = @"SELECT VALUE AVG(p.ListPrice)
FROM AdventureWorksEntities.Products as p";
SELECT VALUE AVG(p.ListPrice)
FROM AdventureWorksEntities.Products AS p
BigCount(expression)
Returns the size of the aggregate including null and duplicate values.
Arguments
Any type.
Return Value
An Int64.
Example
queryString = @"SELECT VALUE BigCount(p.ProductID)
FROM AdventureWorksEntities.Products as p";
SELECT VALUE BigCount(p.ProductID)
FROM AdventureWorksEntities.Products AS p
Count(expression)
Returns the size of the aggregate including null and duplicate values.
Arguments
Any type.
Return Value
An Int32.
Example
queryString = @"SELECT VALUE Count(p.ProductID)
FROM AdventureWorksEntities.Products as p";
SELECT VALUE Count(p.ProductID)
FROM AdventureWorksEntities.Products AS p
Max(expression)
Returns the maximum of the non-null values.
Arguments
A Byte, Int16, Int32, Int64, Byte, Single, Double, Decimal, DateTime, DateTimeOffset, Time, String, Binary.
Return Value
The type of expression, or null if all input values are null values.
Example
queryString = @"SELECT VALUE MAX(p.ListPrice)
FROM AdventureWorksEntities.Products as p";
SELECT VALUE MAX(p.ListPrice)
FROM AdventureWorksEntities.Products AS p
Min(expression)
Returns the minimum of the non-null values.
Arguments
A Byte, Int16, Int32, Int64, Byte, Single, Double, Decimal, DateTime, DateTimeOffset, Time, String, Binary.
Return Value
The type of expression, or null if all input values are null values.
Example
queryString = @"SELECT VALUE MIN(p.ListPrice)
FROM AdventureWorksEntities.Products as p";
SELECT VALUE MIN(p.ListPrice)
FROM AdventureWorksEntities.Products AS p
StDev(expression)
Returns the standard deviation of the non-null values.
Arguments
An Int32, Int64, Double, Decimal.
Return Value
A Double. Null, if all input values are null values.
Example
queryString = @"SELECT VALUE StDev(product.ListPrice)
FROM AdventureWorksEntities.Products AS product
WHERE product.ListPrice > @price";
SELECT VALUE StDev(product.ListPrice)
FROM AdventureWorksEntities.Products AS product
WHERE product.ListPrice > @price
StDevP(expression)
Returns the standard deviation for the population of all values.
Arguments
An Int32, Int64, Double, Decimal.
Return Value
A Double, or null if all input values are null values.
Example
queryString = @"SELECT VALUE StDevP(product.ListPrice)
FROM AdventureWorksEntities.Products AS product
WHERE product.ListPrice > @price";
SELECT VALUE StDevP(product.ListPrice)
FROM AdventureWorksEntities.Products AS product
WHERE product.ListPrice > @price
Sum(expression)
Returns the sum of the non-null values.
Arguments
An Int32, Int64, Double, Decimal.
Return Value
A Double, or null if all input values are null values.
Example
queryString = @"SELECT VALUE Sum(p.ListPrice)
FROM AdventureWorksEntities.Products as p";
SELECT VALUE Sum(p.ListPrice)
FROM AdventureWorksEntities.Products AS p
Var(expression)
Returns the variance of all non-null values.
Arguments
An Int32, Int64, Double, Decimal.
Return Value
A Double, or null if all input values are null values.
Example
queryString = @"SELECT VALUE Var(product.ListPrice)
FROM AdventureWorksEntities.Products AS product
WHERE product.ListPrice > @price";
SELECT VALUE Var(product.ListPrice)
FROM AdventureWorksEntities.Products AS product
WHERE product.ListPrice > @price
VarP(expression)
Returns the variance for the population of all non-null values.
Arguments
An Int32, Int64, Double, Decimal.
Return Value
A Double, or null if all input values are null values.
Example
queryString = @"SELECT VALUE VarP(product.ListPrice)
FROM AdventureWorksEntities.Products AS product
WHERE product.ListPrice > @price";
SELECT VALUE VarP(product.ListPrice)
FROM AdventureWorksEntities.Products AS product
WHERE product.ListPrice > @price
Equivalent functionality is available in the Microsoft SQL Client Managed Provider. For more information, see SqlClient for Entity Framework Functions.
Collection-based aggregates
Collection-based aggregates (collection functions) operate on collections and return a value. For example if ORDERS is a collection of all orders, you can calculate the earliest ship date with the following expression:
min(select value o.ShipDate from LOB.Orders as o)
Expressions inside collection-based aggregates are evaluated within the current ambient name-resolution scope.
Group-based aggregates
Group-based aggregates are calculated over a group as defined by the GROUP BY clause. For each group in the result, a separate aggregate is calculated by using the elements in each group as input to the aggregate calculation. When a group-by clause is used in a select expression, only grouping expression names, aggregates, or constant expressions can be present in the projection or order-by clause.
The following example calculates the average quantity ordered for each product:
select p, avg(ol.Quantity) from LOB.OrderLines as ol
group by ol.Product as p
It's possible to have a group-based aggregate without an explicit group-by clause in the SELECT expression. In this case, all elements are treated as a single group. This is equivalent of specifying a grouping based on a constant. Take, for example, the following expression:
select avg(ol.Quantity) from LOB.OrderLines as ol
This is equivalent to the following:
select avg(ol.Quantity) from LOB.OrderLines as ol group by 1
Expressions inside the group-based aggregate are evaluated within the name-resolution scope that would be visible to the WHERE clause expression.
As in Transact-SQL, group-based aggregates can also specify an ALL or DISTINCT modifier. If the DISTINCT modifier is specified, duplicates are eliminated from the aggregate input collection, before the aggregate is computed. If the ALL modifier is specified (or if no modifier is specified), no duplicate elimination is performed.