Lesson 1: Using Aggregate Functions

Functions that calculate summary values such as an average or a sum are called aggregate functions. SQL Server provides a rich set of aggregate functions for quickly calculating summary values on columns of a table using a single statement.

After this lesson, you will be able to:

• Generate a single summary value by using aggregate functions

Estimated lesson time: 25 minutes

When an aggregate function is executed in a SELECT query, Microsoft SQL Server version 7.0 summarizes the values in a particular column of the result set. A single aggregate value is produced for the entire result set or for groups within the result set. Add the GROUP BY clause to the SELECT statement to return summary values for multiple groups within the selected rows.

Introduction to the Aggregate Functions

The following table briefly describes each of the Transact-SQL aggregate functions:

Aggregate Function	Description
AVG	The average of all the values
COUNT	The number of non-null values
COUNT(*)	The number of rows in the table or group, including null values and duplicates
MAX	The maximum value from all the values
MIN	The minimum value from all the values
SUM	The sum of all the values
STDEV	The statistical deviation of all values
STDEVP	The statistical deviation for the population of all values
VAR	The statistical variance of all values
VARP	The statistical variance for the population of all values

All aggregate functions, except for the COUNT(*) function, return NULL if no rows satisfy the WHERE clause of the SELECT statement. The COUNT(*) function returns a value of 0 if no rows satisfy the WHERE clause.

The data type of a column determines the functions that you can use with it. The following table describes the relationships between functions and data types.

Function	Supported Data Types
COUNT	Any type except uniqueidentifier, text, image, or ntext
MIN and MAX	Char, varchar, datetime, and all numeric data types except the bit data type
SUM, AVG, STDEV, STDEVP, VAR and VARP	All numeric types except the bit data type

NOTE

---

Index frequently aggregated columns to improve query performance. For example, if you frequently use aggregate functions on the quantity column when querying a sales table, indexing the quantity column will improve the performance of these queries.

When you use aggregate functions in the select list of a SELECT statement, you cannot use column names in the same statement, because an aggregate function returns a single value and a column reference returns a value for each row. In the next lesson, you will learn how to group rows on columns that have common values in many rows.

Partial Syntax

SELECT {{AVG | COUNT | MAX | MIN | SUM
    | STDEV | STDEVP | VAR | VARP }(expression | *)} [,Ön]
FROM table_list
[ WHERE search_conditions ]

Example 1

This example calculates the average unit price of all products in the Products table.

 USE Northwind  SELECT AVG(UnitPrice)  FROM Products 

Result

 ---------------------  28.8663  (1 row(s) affected) 

Example 2

This example sums all rows in the Quantity column in the [Order Details] table.

 USE Northwind  SELECT SUM(Quantity)  FROM [Order Details] 

Result

 -----------   51317   (1 row(s) affected) 

Using Aggregate Functions with Null Values

Null values can cause aggregate functions to produce unexpected results. For example, if you execute a SELECT statement that includes a COUNT function on a column that contains 18 rows, two of which contain null values, the COUNT function returns a result of 16. SQL Server ignores the two rows that contain null values.

Therefore, use caution when using aggregate functions on columns that contain null values, because the result set may not be representative of your data. However, if you decide to use aggregate functions with null values, consider the following facts:

All SQL Server aggregate functions, with the exception of the COUNT(*) function, ignore null values in columns. The COUNT(*) function counts all rows, even if every column contains a null value. For example, if you execute a SELECT statement that includes the COUNT(*) function on a column that contains 18 rows, two of which contain null values, the COUNT(*) function returns a result of 18.

Example 1

This example lists the number of employees in the Employees table.

 USE Northwind  SELECT COUNT(*)  FROM Employees 

Result

 -----------   9   (1 row(s) affected) 

Example 2

This example lists the number of employees who have a null value in the ReportsTo column in the Employees table, indicating that no reporting manager is defined for that employee.

NOTE

---

You should always use IS NULL or IS NOT NULL when performing comparisons with null values. SET ANSI_NULLS controls SQL Server s behavior when performing comparisons to null values. If you use = to perform a comparison to a null value, the result will be null rather than true or false if SET ANSI_NULLS is ON, which is the default setting. It is important to know the compatibility settings of your SQL Server, because the settings affect how SQL Server manages null values.

 USE Northwind  SELECT COUNT(*)  FROM Employees  WHERE ReportsTo IS NULL 

Result

 -----------   1 (1 row(s) affected) 

Lesson Summary

Aggregate functions return a summary value for the values in a column. If there are no rows in the query, all aggregate functions except COUNT(*) return NULL; COUNT(*) returns 0 in this case. Null values are ignored by all aggregate functions except COUNT(*).