Share via


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

SELECT VALUE AVG(p.ListPrice) FROM AdventureWorksEntities.Product as p

BigCount( expression )

Returns the size of the aggregate including null and duplicate values.

Arguments

Any type.

Return Value

An Int64.

Example

SELECT VALUE BigCount(p.ProductID) FROM AdventureWorksEntities.Product as p

Count( expression )

Returns the size of the aggregate including null and duplicate values.

Arguments

Any type.

Return Value

An Int32.

Example

SELECT VALUE Count(p.ProductID) FROM AdventureWorksEntities.Product 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. Null, if all input values are null values.

Example

SELECT VALUE Max(p.ListPrice) FROM AdventureWorksEntities.Product 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 and Binary

Return Value

The type of expression. Null, if all input values are null values.

Example

SELECT VALUE Min(p.ListPrice) FROM AdventureWorksEntities.Product 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

SELECT VALUE StDev(product.ListPrice)

FROM AdventureWorksEntities.Product AS product

where product.ListPrice > 2.0M

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

SELECT VALUE Sum(p.ListPrice) FROM AdventureWorksEntities.Product as p

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.

See Also

Concepts

Canonical Functions (Entity SQL)