Home > Data Access / ADO.NET > Structured Query Language (SQL) Basics > Using SQL Aggregate Functions

---

SQL provides the following aggregate functions that are available for use with a SELECT item that is a column or an expression involving a column:

Using the SQL aggregate functions, you can determine various statistics on sets of values. You can use these functions as a columnItem in a query when creating a DataSet object.

The argument colExpr represents a string expression identifying the column that contains the numeric data you want to average, count, evaluate, or sum, or an expression that performs a calculation using the data in that column.

Operands in colExpr can include the name of a table column, a constant, or a function ( which can be either intrinsic or user-defined but not one of the other SQL aggregate functions ).

Run Sample | View Source

Now let’s explore that in detail.

Avg ( colExpr ) calculates the arithmetic mean of a set of values contained in a specified column on a query.

SELECT ProductType, AVG( UnitPrice ) AS [Average Unit Price] 
   FROM Products GROUP BY ProductType

The average calculated by Avg is the the sum of the values divided by the number of values ( arithmetic mean ). The Avg function does not include any Null columns in the calculation.

Count ( colExpr ) calculates the number of records returned by a query.

You can use Count to count the number of records in the underlying query, or count groups of records.

SELECT Order_Details.ProductID, ProductCode, 
   ProductName, COUNT( * ) AS [Total Records] 
   FROM Order_Details INNER JOIN Products 
   ON Order_Details.ProductID=Products.ProductID 
   GROUP BY Order_Details.ProductID, ProductCode, ProductName 
   ORDER BY COUNT( * ) DESC

Although colExpr can perform a calculation on a column, Count simply tallies the number of records. It does not matter what values are stored in the records.

The Count function does not count records that have Null columns unless colExpr is the asterisk ( * ) wildcard character. If you use an asterisk, Count calculates the total number of records, including those that contain Null columns. Count( * ) is considerably faster than Count( columnName ). Do not enclose the asterisk in quotation marks ( ' ' ).

If colExpr identifies multiple columns, the Count function counts a record only if at least one of the columns is not Null. If all of the specified columns are Null, the record is not counted. Separate the column names with an ampersand ( & ).

The following example shows how you can limit the count to records in which either ShippedDate or Freight is not Null:

SELECT Count( 'ShippedDate & Freight' ) AS [Not Null] FROM Orders;

Min ( colExpr ), Max ( colExpr ) return the minimum or maximum of a set of values contained in a specified column on a query.

You can use Min and Max to determine the smallest and largest values in a column based on the specified aggregation, or grouping. For example, you could use these functions to return the lowest and highest unit price.

SELECT ProductType, MIN( UnitPrice ) AS [Minimun Price of Items], 
   MAX( UnitPrice ) AS [Maximum Price of Items] 
   FROM Products GROUP BY ProductType

If there is no aggregation specified, then the entire table is used.

The Sum function totals the values of a given columnItem. Sum ( colExpr ) returns the sum of a set of values contained in a specified column or an expression involving a column.

The aggregate functions sample above shows use of Sum( ) to determine the total orders for each ProductID. The example below shows how you can sum the total orders for each group of OrderID's.

SELECT Order_Details.ProductID, ProductCode, ProductName, 
   SUM( Quantity ) AS [Total Orders],
   SUM( Quantity*Order_Details.UnitPrice ) 
      AS [Total Sales in US Dollars] 
   FROM Order_Details INNER JOIN Products 
   ON Order_Details.ProductID=Products.ProductID 
   GROUP BY Order_Details.ProductID, ProductCode, ProductName 
   ORDER BY SUM( Quantity*Order_Details.UnitPrice ) DESC

Run Sample | View Source

The Sum function ignores records that contain Null columns.

Note that you cannot nest aggregate functions.

  Optimizing Queries