Types of Functions
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
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
|
Function |
Usage |
|---|---|
AVG(expression) |
Computes the average value of a column by the expression |
COUNT(expression) |
Counts the rows defined by the expression |
COUNT(*) |
Counts all rows in the specified table or view |
MIN(expression) |
Finds the minimum value in a column by the expression |
MAX(expression) |
Finds the maximum value in a column by the expression |
SUM(expression) |
Computes the sum of column values by the expression |
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 ...