Aggregate Functions (SqlClient for Entity Framework)

The .NET Framework Data Provider for SQL Server (SqlClient) provides aggregate functions. Aggregate functions perform calculations on a set of input values and return a value. These functions are in the SqlServer namespace, which is available when you use SqlClient. A provider's namespace property allows the Entity Framework to discover which prefix is used by this provider for specific constructs, such as types and functions.

The following are the SqlClient aggregate functions.

AVG(expression)

Returns the average of the values in a collection. Null values are ignored.

Arguments

An Int32, Int64, Double, and Decimal.

Return Value

The type of expression.

Example

SELECT VALUE SqlServer.AVG(p.ListPrice) FROM 
AdventureWorksEntities.Products as p 
SELECT VALUE SqlServer.AVG(p.ListPrice) 
FROM AdventureWorksEntities.Products AS p 

CHECKSUM_AGG(collection)

Returns the checksum of the values in a collection. Null values are ignored.

Arguments

A Collection(Int32).

Return Value

An Int32.

Example

SELECT VALUE SqlServer.Checksum_Agg(cast(product.ListPrice as Int32)) 
FROM AdventureWorksEntities.Products AS product 
WHERE product.ListPrice > cast(@price as Decimal) 
SELECT VALUE SqlServer.Checksum_Agg(cast(product.ListPrice AS Int32)) 
FROM AdventureWorksEntities.Products AS product 
WHERE product.ListPrice > cast(@price AS Decimal) 

COUNT(expression)

Returns the number of items in a collection as an Int32.

Arguments

A Collection<T>, where T is one of the following types:

Boolean Double DateTime DateTimeOffset
Time String Binary Guid (not returned in SQL Server 2000)

Return Value

An Int32.

Example

ANYELEMENT(SELECT VALUE SqlServer.COUNT(product.ProductID) 
FROM AdventureWorksEntities.Products AS product 
WHERE SqlServer.CEILING(product.ListPrice) == 
SqlServer.FLOOR(product.ListPrice)) 

[!code-sql[DP EntityServices Concepts#SQLSERVER_COUNT](~/samples/snippets/tsql/VS_Snippets_Data/dp entityservices concepts/tsql/entitysql.sql#sqlserver_count)

COUNT_BIG(expression)

Returns the number of items in a collection as a bigint.

Arguments

A Collection(T), where T is one of the following types:

Boolean Double DateTime DateTimeOffset
Time String Binary Guid (not returned in SQL Server 2000)

Return Value

An Int64.

Example

ANYELEMENT(SELECT VALUE SqlServer.COUNT_BIG(product.ProductID) 
FROM AdventureWorksEntities.Products AS product 
WHERE SqlServer.CEILING(product.ListPrice) == 
SqlServer.FLOOR(product.ListPrice)) 
ANYELEMENT(SELECT VALUE SqlServer.COUNT_BIG(product.ProductID) 
FROM AdventureWorksEntities.Products AS product 
WHERE SqlServer.CEILING(product.ListPrice) == 
SqlServer.FLOOR(product.ListPrice)) 

MAX(expression)

Returns the maximum value the collection.

Arguments

A Collection(T), where T is one of the following types:

Boolean Double DateTime DateTimeOffset
Time String Binary

Return Value

The type of expression.

Example

SELECT VALUE SqlServer.MAX(p.ListPrice) 
FROM AdventureWorksEntities.Products as p
SELECT VALUE SqlServer.MAX(p.ListPrice) 
FROM AdventureWorksEntities.Products AS p

MIN(expression)

Returns the minimum value in a collection.

Arguments

A Collection(T), where T is one of the following types:

Boolean Double DateTime DateTimeOffset
Time String Binary

Return Value

The type of expression.

Example

SELECT VALUE SqlServer.MIN(p.ListPrice) 
FROM AdventureWorksEntities.Products as p
SELECT VALUE SqlServer.MIN(p.ListPrice) 
FROM AdventureWorksEntities.Products AS p

STDEV(expression)

Returns the statistical standard deviation of all values in the specified expression.

Arguments

A Collection(Double).

Return Value

A Double.

Example

SELECT VALUE SqlServer.STDEV(product.ListPrice) 
FROM AdventureWorksEntities.Products AS product 
WHERE product.ListPrice > cast(@price as Decimal) 
SELECT VALUE SqlServer.STDEV(product.ListPrice) 
FROM AdventureWorksEntities.Products AS product 
WHERE product.ListPrice > cast(@price AS Decimal) 

STDEVP(expression)

Returns the statistical standard deviation for the population for all values in the specified expression.

Arguments

A Collection(Double).

Return Value

A Double.

Example

SELECT VALUE SqlServer.STDEVP(product.ListPrice) 
FROM AdventureWorksEntities.Products AS product 
WHERE product.ListPrice > cast(@price as Decimal) 
SELECT VALUE SqlServer.STDEVP(product.ListPrice) 
FROM AdventureWorksEntities.Products AS product 
WHERE product.ListPrice > cast(@price AS Decimal) 

SUM(expression)

Returns the sum of all the values in the collection.

Arguments

A Collection(T) where T is one of the following types: Int32, Int64, Double, Decimal.

Return Value

The type of expression.

Example

SELECT VALUE SqlServer.SUM(p.ListPrice) 
FROM AdventureWorksEntities.Products as p
SELECT VALUE SqlServer.SUM(p.ListPrice) 
FROM AdventureWorksEntities.Products AS p

VAR(expression)

Returns the statistical variance of all values in the specified expression.

Arguments

A Collection(Double).

Return Value

A Double.

Example

SELECT VALUE SqlServer.VAR(product.ListPrice) 
FROM AdventureWorksEntities.Products AS product 
WHERE product.ListPrice > cast(@price as Decimal) 
SELECT VALUE SqlServer.VAR(product.ListPrice) 
FROM AdventureWorksEntities.Products AS product 
WHERE product.ListPrice > cast(@price AS Decimal) 

VARP(expression)

Returns the statistical variance for the population for all values in the specified expression.

Arguments

A Collection(Double).

Return Value

A Double.

Example

SELECT VALUE SqlServer.VARP(product.ListPrice) 
FROM AdventureWorksEntities.Products AS product 
WHERE product.ListPrice > cast(@price as Decimal) 
SELECT VALUE SqlServer.VARP(product.ListPrice) 
FROM AdventureWorksEntities.Products AS product 
WHERE product.ListPrice > cast(@price AS Decimal) 

See also

For more information about the aggregate functions that SqlClient supports, see the documentation for the SQL Server version that you specified in the SqlClient provider manifest:

SQL Server 2005: Aggregate Functions (Transact-SQL)
SQL Server 2008 and later: Aggregate Functions (Transact-SQL)
Entity SQL Language
Aggregate Canonical Functions