In addition to operators, SQLite supports both built-in and user-defined function calls. There are two categories of function calls. Scalar functions are called with a specific set of parameters and return a value, just like functions in almost any other expression language. Scalar functions can be used in just about any context in any SQLite expression. An example of a scalar function is abs(), which returns the absolute value of a numeric parameter.

There are also aggregate functions, which are used to collapse or summarize groups of rows. Aggregate functions can only be used in expressions that define the result set or HAVING clause of a SELECT statement. Aggregates are often used in conjunction with GROUP BY clauses. In essence, an aggregate function is called many times with different input values, but returns only one value per dataset. An example of an aggregate function is avg(), which computes the average value for a sequence of numeric inputs.

The syntax for a function call looks like this:

As with many expression languages, a function can be called by naming the function and providing a list of zero or more comma-separated parameter expressions within a set of parentheses. In some contexts, the special syntax of a single * character can also be used in place of a parameter list. Like the result set definition of a SELECT statement, this has an implied meaning of “everything.”

The optional keyword DISTINCT can also be included before the first parameter. This is only relevant for aggregate functions. If present, it will verify that each set of parameters passed in to an aggregate will be unique and distinct. The keyword has no effect when used with scalar functions.

For a full listing of all the built-in functions that SQLite supports, see Appendix E.