SQL and Relational Theory, 2nd Edition

AGGREGATE OPERATORS

In a sense this section is a bit of a digression, because the operators to be discussed aren’t relational but scalar—they return a scalar result.^[98] But I do need to say something about them before I can get back to the main theme of the chapter.

An aggregate operator in the relational model is an operator that derives a single value from the “aggregate” (i.e., the bag or set) of values appearing within some attribute within some relation—or, in the case of COUNT, which is slightly special, from the “aggregate” that’s the entire relation. Here are two examples:

I’ll focus on the Tutorial D statements on the left first. Given our usual sample values, the first assigns the value 5 (the number of tuples in the current value of relvar S) to the variable X; the second assigns the value 3 (the number of tuples in the projection of the current value of relvar S on {STATUS}, which is to say the number of distinct STATUS values in that current value) to the variable Y.

In general, a Tutorial D aggregate operator invocation looks like this:

<agg op name> ( <relation exp> [, <exp> ] )

Legal <agg op name>s include COUNT, SUM, AVG, MAX, MIN, AND, OR, and XOR.^[99] Within the <exp>, an <attribute ref> can appear wherever a literal would be allowed. That <exp> must be omitted if the <agg op name> is COUNT; otherwise, it can be omitted only if the <relation exp> denotes a relation ...