Aggregate Canonical Functions

  • Article

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.Products 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.Products 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.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. Null, if all input values are null values.

Example

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. Null, if all input values are null values.

Example

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

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. Null, if all input values are null values.

Example

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. Null, if all input values are null values.

Example

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. Null, if all input values are null values.

Example

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. Null, if all input values are null values.

Example

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 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.

See Also

Concepts

Canonical Functions