SQL Server Aggregate Functions

Aggregate Functions : Aggregate functions perform a calculation on a set of values and return a single value. Except for COUNT, aggregate functions ignore null values. Aggregate functions are frequently used with the GROUP BY clause of the SELECT statement.

Aggregate functions can be used as expressions only in the following:

·         The select list of a SELECT statement (either a subquery or an outer query).

·         A HAVING clause.

AVG : Returns the average of the values in a group. Null values are ignored.

Examples of AVG function :

Using avg function in SELECT clause:-

SELECT AVG(UnitsInStock) FROM   Products

Using avg function with DISTINCT:-

SELECT AVG ( DISTINCT UnitsInStock FROM Products

Using avg function in SELECT clause with GROUP BY clause:-

SELECT ProductName,AVG(Quantity)FROM   Invoices

GROUP  BY ProductName

MIN : Returns the minimum value in the expression.

Examples of MIN function :

For example, the following returns the least amount from the FactFinance table:

SELECT MIN(amount) FROM FactFinance

Results:

-1121918

Note that the parameter of MIN can be any valid expression, including string columns, as in the following:

SELECT MIN(EnglishProductName)FROM DimProduct

Results:

Adjustable Race

SUM : Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored.

Examples of SUM function :

The following query returns the sum of amounts from FactFinance table:

SELECT SUM(Amount)FROM FactFinance

Results:

1358640412.7

The next example uses DISTINCT keyword to return the sum of distinct values:

SELECT SUM(DISTINCT Amount)FROM FactFinance

Results:

1251597458.19

COUNT : Returns the number of items in a group. COUNT works like the COUNT_BIG function. The only difference between the two functions is their return values. COUNT always returns an int data type value. COUNT_BIG always returns a bigint data type value.

Examples of COUNT function :

If you specify a "*" as the criterion, COUNT returns the total number of rows in a table; for example, the following query counts rows in the FactFinance table of Adventure Works DW database:

SELECT COUNT(*)FROM FactFinance

Results:

39409

If you join multiple tables then COUNT(*) returns the number of rows satisfying the join criterion, as in the following:

SELECT COUNT(*)FROM FactFinance a INNER JOIN DimOrganization b

ON a.OrganizationKey = b.OrganizationKey

COUNT(*) cannot be used with DISTINCT; nor can you specify any other parameter - this variation of the function automatically counts every single row in a single or multiple joined tables.

Unlike all other aggregate functions, COUNT does not ignore NULL values.

If you need to find the count of unique items within a column in a table use COUNT (DISTINCT column_name) syntax. For example, the following query counts unique organization keys within the FactFinance table:

SELECT COUNT(DISTINCT OrganizationKey)FROM FactFinance

Results:

9

MAX : Returns the maximum value in the expression.

Examples of MAX function :

For example, the following returns the greatest amount from the FactFinance table:

SELECT MAX(amount)FROM FactFinance

Results:

4820988