Aggregate Canonical Functions (Entity SQL)
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.
The following table shows the aggregate Entity SQL canonical functions.
| Function | Description |
|---|---|
Avg( expression ) |
Returns the average of the non-null values. Arguments An Int32, Int64, Double, and Decimal. Return Value The type of expression. Null, if all input values are null values. Example
|
BigCount( expression ) |
Returns the size of the aggregate including null and duplicate values. Arguments Any type. Return Value An Int64. Example
|
Count( expression ) |
Returns the size of the aggregate including null and duplicate values. Arguments Any type. Return Value An Int32. Example
|
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. Null, if all input values are null values. Example
|
Min( expression ) |
Returns the minimum of the non-null values. Arguments A Byte, Int16, Int32, Int64, Byte, Single, Double, Decimal, DateTime, DateTimeOffset, Time, String and Binary Return Value The type of expression. Null, if all input values are null values. Example
|
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
|
Sum( expression ) |
Returns the sum of the non-null values. Arguments An Int32, Int64, Double, Decimal. Return Value A Double. Null, if all input values are null values. Example
|
Equivalent functionality is available in the Microsoft SQL Client Managed Provider. For more information, see .NET Framework Data Provider for SQL Server (SqlClient) for the 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 is possible to have a group-based aggregate without an explicit group-by clause in the SELECT expression. In this case, all elements will be treated as a single group. This is equivalent to the case of specifying a grouping based on 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.
Like 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.
Collection-Based vs. Group-Based Aggregates
Collection-based aggregates are the preferred mode of specifying aggregates in Entity SQL. However, group-based aggregates are also supported to make it easier for SQL Server users to learn to use Entity SQL.
Similarly, specifying DISTINCT (or ALL) as a modifier to the aggregate input is supported for SQL-like behavior, but the preferred mechanism is to use the set() operator instead.