T-SQL Functions | Microsoft SQL Server 2000 Administrators Companion

3 4

Now that you are familiar with the basic clauses you can use with the SELECT statement (a few more will be covered in Chapter 20), let's look at some of the T-SQL functions that you can use in the SELECT clause to give you more flexibility in creating queries. These functions are grouped in several categories, including configuration, cursor, date and time, security, metadata, system, system statistical, text and image, mathematical, rowset, string, and aggregate functions. These functions either perform calculations, conversions, or some other operations, or they return certain information. Many functions are available, but in this section, we'll examine only the aggregate functions, which are commonly used.

MORE INFO
For more information about this category and the other categories of functions, use the Index tab in SQL Server Books Online to look up "functions."

Aggregate Functions

As mentioned, an aggregate function performs a calculation on a set of values and returns a single value. Aggregate functions can be specified in the select list and are most often used when the statement contains a GROUP BY clause. Some of the preceding examples used the aggregate functions AVG and COUNT. Table 14-3 lists the available aggregate functions.

Table 14-3. Aggregate functions

Function	Description
AVG	Returns the average of the values in an expression; ignores all null values.
COUNT	Returns the number of items (same as the number of rows) in an expression.
COUNT_BIG	Same as COUNT, except it returns the count in a bigint data type rather than an int.
GROUPING	Returns an additional special column; used only when the GROUP BY clause contains either the CUBE or ROLLUP operator. For more information, use the Index tab in SQL Server Books Online to find GROUPING keyword.
MAX	Returns the maximum value of an expression.
MIN	Returns the minimum value of an expression.
STDEV	Returns the statistical standard deviation of all values in an expression. This function assumes that the values used in the calculation are a sample of the entire population.
STDEVP	Returns the statistical standard deviation for the population for all values in an expression. This function assumes that the values used in the calculation are the entire population.
SUM	Returns the sum of all the values in an expression.
VAR	Returns the statistical variance of all values in an expression. This function assumes that the values used in the calculation are a sample of the entire population.
VARP	Returns the statistical variance for the population for all values in an expression. This function assumes that the values used in the calculation are the entire population.

The COUNT function has a special use: to count all of the rows in a table. To do this, use (*) after COUNT, as shown here:

  SELECT   COUNT(*) FROM     publishers GO

The result set is shown here:

  —————          8

This result set indicates that the publishers table contains eight rows.

The AVG, COUNT, MAX, MIN, and SUM functions can be used with the optional keywords ALL and DISTINCT. For each of these functions, ALL indicates that the function should be applied to all values in the expression, and DISTINCT indicates that duplicate values should be counted only once in the calculation. The default option is ALL.

The aggregate functions are basically self-explanatory. The following example uses the MAX and MIN functions together in the select list to find the price difference between the most expensive book and the least expensive book:

  SELECT   MAX(price) - MIN(price) AS "Price Difference" FROM     titles GO

The result set is shown here:

  Price Difference ————————————                    19.96

In this example, SUM is used to find the total quantity of items ordered per store:

  SELECT   stores.stor_name, SUM(sales.qty) AS "Total Items Ordered"  FROM     sales, stores WHERE    sales.stor_id = stores.stor_id GROUP BY stor_name GO

The result set is shown here:

  stor_name                                Total Items Ordered ———————————————————— ————————— Barnum's                                                 125 Bookbeat                                                  80 Doc-U-Mat: Quality Laundry and Books                     130 Eric the Read Books                                        8 Fricative Bookshop                                        60 News & Brews                                              90 (6 rows affected)

NOTE
Remember that SQL Server offers many types of functions. If you need to perform a specific operation, check SQL Server Books Online to see whether a built-in function already exists.