Structured Query Language/SELECT: Predefined Functions

There are two groups of predefined functions:
 * aggregate functions. They work on a set of rows, which means they receive one value for each row of a set of rows and returns one value for the whole set. If they are called in the context of a GROUP BY clause, they are called once per group, else once for all rows.
 * scalar functions. They work on single rows, which means they receive one value of a single row and returns one value for each of them.

Aggregate functions
They work on a set of rows and return one single value like the number of rows, the highest or lowest value, the standard deviation, etc. The most important aggregate functions are:

As an example we retrieve the maximum weight of all persons:

A Word of Caution

Aggregate functions result in one value for a set of rows. Therefore it is not possible to use them together with 'normal' columns in the projection (the part behind SELECT keyword). If we specify, for example, we try to instruct the DBMS to show a lot of rows containing the lastname simultaneously with one value. This is a contradiction and the system will throw an exception. We can use a lot of aggregate functions within one projection but we are not allowed to use them together with 'normal' columns.

Grouping

If we use aggregate functions in the context of commands containing a GROUP BY, the aggregate functions are called once per group.

In such cases the GROUP BY column(s) may be displayed as it is impossible that they change within the group.

The NULL special marker
If a row contains no value (it holds the NULL special marker) in the named column, the row is not part of the computation.

ALL vs. DISTINCT
The complete signatures of the functions are a little more detailed. We can prepend the column name with one of the two key words ALL or DISTINCT. If we specify ALL, which is the default, every value is part of the computation, else only those, which are distinct from each other.

Hint
The standard defines some more aggregate functions to compute statistical measures. Also the keywords ANY, EVERY and SOME formally are defined as aggregate functions. We will discuss them on a separate page.

Scalar functions
Scalar functions act on a 'per row basis'. They are called once per row and they return one value per call. Often they are grouped according to the data types they act on:


 * String functions
 * SUBSTRING( FROM FOR ) returns a string starting at position (first character counts '1') in the length of.
 * UPPER returns the uppercase equivalent of the column value.
 * LOWER returns the lowercase equivalent of the column value.
 * CHARACTER_LENGTH returns the length of the column value.
 * TRIM returns the column value without leading and trailing spaces.
 * TRIM(LEADING FROM ) returns the column value without leading spaces.
 * TRIM(TRAILING FROM ) returns the column value without trailing spaces.


 * Numeric functions
 * SQRT returns the square root of the column value.
 * ABS returns the absolute value of the column value.
 * MOD returns the remaining of column value divided by divisor.
 * others: FLOOR, CEIL, POWER, EXP, LN.


 * Date, Time & Interval functions
 * EXTRACT(month FROM date_of_birth) returns the month of column date_of_birth.


 * build-in functions. They do not have any input parameter.
 * CURRENT_DATE returns the current date.
 * CURRENT_TIME returns the current time.

There is another wikibook where those functions are shown in detail. The data type of the return value is not always identical to the type of the input, e.g. 'character_length' receives a string and returns a number.

Here is an example with some scalar functions:

Exercises
What is the hightest id used so far in the hobby table?

Which lastname will occur first in an ordered list?

Are there aggregate functions where it makes no difference to use the ALL or the DISTINCT key word?

Show persons with a short firstname (up to 4 characters).

Show firstname, lastname and the number of characters for the concatenated string. Find two different solutions. You may use the character_length function to compute the length of strings and the concat function to concatenate strings.