## BuiltIn Functions

Just as VBA incorporates many intrinsic functions such as Val, Str, UCase, and Len, SQL provides many built-in functions for determining information on your result sets. You learn about these SQL aggregate functions in this section:

The AVG function takes an expression such as a column name for a parameter and returns the mean value in a column or other expression.

SELECT AVG(UnitPrice)

FROM Products;

The preceding SQL statement returns a single value, which is the mean value of the UnitPrice column in the Products table. Output is seen in Figure 8.9.

Using the AVG function to calculate the mean value of a column.

Using the AVG function to calculate the mean value of a column.

Notice in Figure 8.9 that the column heading gives no clue as to the meaning of the SQL statement's return value. To correct this, simply use the AS clause.

SELECT AVG(UnitPrice) AS [Average Unit Price] FROM Products;

The COUNT function is a very useful function for determining how many records are returned by a query. For example, the following SQL query uses the COUNT function to determine how many customer records are in the Customers table.

SELECT COUNT(*) AS [Number of Customers] FROM Customers;

Figure 8.10 reveals the output from the COUNT function in the preceding SQL statement. Note that it's possible to supply a column name in the COUNT function, but the wildcard character (*) performs a faster calculation on the number of records found in a table.

Displaying the result of a COUNT function.

Figure 8.10 reveals the output from the COUNT function in the preceding SQL statement. Note that it's possible to supply a column name in the COUNT function, but the wildcard character (*) performs a faster calculation on the number of records found in a table.

Displaying the result of a COUNT function.

The FIRST and LAST functions return the first and last records in a result set, respectively. Because records are not necessarily stored in alphanumeric order, the FIRST and LAST functions may produce seemingly unexpected results. The results, however, are accurate. These functions report the first and last expressions in a result set as stored in a database and returned by the SQL query.

SELECT FIRST(LastName) AS [First Employee Last Name],

LAST(LastName) AS [Last Employee Last Name] FROM Employees;

The preceding SQL statement uses the FIRST and LAST functions to retrieve the first and last names (specifically the last name) of employee records in the Employees table. Output is seen in Figure 8.11.

Using the FIRST and LAST functions to retrieve the first and last values of a result set.

To determine the minimum and maximum values of an expression in SQL, use the MIN and MAX functions, respectively. Like other SQL functions, the MIN and MAX functions take an expression and return a value. The next SQL statement uses these two functions to determine the minimum and maximum unit prices found in the Products table. Output is seen in Figure 8.12.

SELECT MIN(UnitPrice) AS [Minimum Unit Price], MAX(UnitPrice) AS [Maximum Unit Price] FROM Products;

Retrieving the minimum and maximum values from an expression using the MIN and MAX functions.

Retrieving the minimum and maximum values from an expression using the MIN and MAX functions.

The SUM function takes an expression as argument and returns the sum of values. The SUM function is used in the next SQL statement, which takes a computed field as an argument to derive the sum of subtotals in the Order Details table.

SELECT SUM(UnitPrice * Quantity) AS [Sum of Sub Totals] FROM [Order Details];

Output from the SQL statement using the SUM function is in Figure 8.13.

Displaying the output of the SUM function.

Displaying the output of the SUM function.

The last built-in function for this section is DISTINCT, which returns a distinct set of values for an expression. To demonstrate, if I want to find a unique list of countries for the suppliers in the Northwind database, I need to sift through every record in the Suppliers table and count each distinct country name. Or, I could use the DISTINCT function to return a distinct value for each country in the Country column.

SELECT DISTINCT(Country) FROM Suppliers;

## Post a comment