Contents

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.

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:

Signatur

Semantic

COUNT(*)

The number of rows

COUNT(<column name>)

The number of rows where <column name> contains a value (IS NOT NULL). The elimination of rows with the NULL special marker in the considered column applies to all aggregate functions.

MIN(<column name>)

Lowest value. In the case of strings according to the sequence of characters.

MAX(<column name>)

Highest value. In the case of strings according to the sequence of characters.

SUM(<column name>)

Sum of all values

AVG(<column name>)

Arithmetic mean

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

SELECTMAX(weight)FROMperson;

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 key word). If we specify, for example,

SELECTlastname,SUM(weight)FROMperson;

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.

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.

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

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

CURRENT_TIME() returns the currente 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.

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

Click to see solution

Yes. min(ALL <column name>) leads to the same result as min(DISTINCT <column name>) asit makes no difference whether the smallest value occurs one or more times. The same is true for max().

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

Click to see solution

-- We can use functions as part of the WHERE clause.SELECT*FROMpersonWHEREcharacter_length(firstname)<=4;-- Hint: Some implementations use a different function name: length() or len().

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.

Click to see solution

-- Addition of the computed length. Hint: Some implementations use a different function name: length() or len().SELECTfirstname,lastname,character_length(firstname)+character_length(lastname)FROMperson;-- length of the concatenated stringSELECTfirstname,lastname,character_length(concat(firstname,lastname))FROMperson;-- show both solutions togetherSELECTfirstname,lastname,character_length(firstname)+character_length(lastname)asL1,character_length(concat(firstname,lastname))asL2FROMperson;