There are several basic types and categories of functions in SQL99 and vendor implementations of SQL. The basic types of functions are:

- Aggregate functions
Operate against a collection of values, but return a single, summarizing value.

- Scalar functions
Operate against a single value, and return a single value based on the input value. Some scalar functions, CURRENT_TIME for example, do not require any arguments.

Aggregate
functions return a single value based upon a set of other values. If
used among many other expressions in the item list of a
*SELECT* statement, the
*SELECT* must have a *GROUP BY*
clause. No *GROUP BY* clause is required if the
aggregate function is the only value retrieved by the
*SELECT* statement. The supported aggregate
functions and their syntax are listed in Table 4.1.

Table 4-1. SQL99 Aggregate Functions

Technically speaking, ANY, EVERY, and SOME are considered aggregate functions. However, they have been discussed as range search criteria since they are most often used that way. Refer to the SELECT . . . WHERE topic in the previous chapter for more information on these functions.

The number of values processed by an aggregate varies depending on the number of rows queried from the table. This behavior makes aggregate functions different from scalar functions, which require a fixed number and fixed type of parameters.

The general syntax of an aggregate function is:

aggregate_function_name ( [ALL | DISTINCT] expression )

The aggregate function name may be AVG,
COUNT, MAX,
MIN, or SUM. The**
**ALL** **clause, which
is the default behavior and does not actually need to be specified,
evaluates all rows when aggregating the value of the function. The
DISTINCT clause uses only distinct values when
evaluating the function.

Start Free Trial

No credit card required