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 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.

Get SQL in a Nutshell now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.