Using Functions
SQL Server provides a number of functions you can use to get values from the database.
For example, you can use the COUNT() function to get the number of rows in a table.
The various functions are split into the categories shown in Table 4.2
.
Table 4.2: FUNCTIONS
FUNCTION
CATEGORY
DESCRIPTION
Aggregate Return information based on one or more rows in a table.
Mathematical Perform calculations.
String Perform string manipulations.
Date and time Work with dates and times.
System Return information on SQL Server.
Configuration Return information on the configuration of the server.
Cursor Return information on cursors.
Metadata Return information on the database and the various database
items, such as tables.
Security Return information on the database users and roles.
System statistical Return statistical information on SQL Server.
Text and image Perform text and image manipulations.
You'll learn about the first five functions in the following sections. The other categories
of functions are beyond the scope of this book, as they are of primary interest to database
administrators. You can learn about those functions in the SQL Server Online Books
documentation.
Using Aggregate Functions
Earlier, you saw the use of the COUNT() aggregate function to get the number of rows.
COUNT() and some other aggregate functions you can use with SQL Server are listed in
Table 4.3
. The expression you may pass to the aggregate functions is typically a single
column, but it can also be a calculated field. ALL means that the function is applied to all

VAR(expression) Returns the variance for all the values.
VARP(expression) Returns the variance for the population of all the
values.
Let's consider examples that use some of the aggregate functions.
You use the AVG() function to get the average value. For example, the following
statement gets the average of the UnitPrice column of the Products table using the AVG()
function:
SELECT AVG(UnitPrice)
FROM Products;
This example returns 28.8663. Since ALL is the default used with functions, this example
uses every row in the Products table when performing the calculation. If you wanted to
just use unique values in the calculation, then you use the DISTINCT option, as shown in
the following example:
SELECT AVG(DISTINCT UnitPrice)
FROM Products;
This example returns 31.4162, slightly higher than the previous result because only
unique values are used this time.
In addition to passing a column to a function, you can also pass a calculated field. For
example, the following statement passes the calculated field UnitPrice * 1.20 to the
AVG() function:
SELECT AVG(UnitPrice * 1.20)
FROM Products;

This example returns 34.639636; the average after the UnitPrice values have been
increased 20 percent.
You can limit the rows passed to a function using a WHERE clause. For example, the
following SELECT statement calculates the average UnitPrice value for the rows with a
CategoryID of 1:
SELECT AVG(UnitPrice)
FROM Products

SELECT MAX(UnitPrice), MIN(UnitPrice)
FROM Products;
This example returns 263.5000 and 2.5000 for the respective maximum and minimum
values.
You use the SUM() function to get the total of any non-null values. For example, the
following statement gets the sum of the UnitPrice column values for each group of rows
using the SUM() function:
SELECT SupplierID, SUM(UnitPrice) AS SumUnitPrice
FROM Products
GROUP BY SupplierID;
The GROUP BY clause of this example returns one row for each block of rows with
identical SupplierID column values. The SUM() function then adds up the UnitPrice
column values for all the rows within each block and returns a single value. For example,
SUM() returns 47.0000 for the group where the SupplierID is 1. This is the sum of the
UnitPrice column values for all the rows where the SupplierID is 1. Similarly, SUM()
returns 81.4000 where the SupplierID is 2, and so on. The AS clause in this example
names the results returned by the SUM() function as SumUnitPrice.
Figure 4.3
shows the results of this SELECT statement.