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 of degree one, in which case an <exp> consisting of a reference to the sole attribute of that relation is assumed. Here are some examples:

Now, you might be surprised to hear me claim that SQL doesn’t really support aggregate operators at all! I say this knowing full well that most people would consider expressions like those on the right above to be, precisely, SQL aggregate operator invocations.^[100] But they aren’t. Let me explain. As we know, the counts are 5 and 3, respectively. But those SQL expressions don’t evaluate to those counts as such, as true aggregate operator invocations would; rather, they evaluate to tables that contain those counts. More precisely, each yields a table with one row and one column, and the sole value in that row is the actual count:^[101]

As you can see, therefore, the SELECT expressions really don’t represent aggregate operator invocations as such; at best, they represent only approximations to such invocations. In fact, aggregation is treated in SQL as if it were a special case of summarization. Of course, I haven’t discussed summarization yet; for present purposes, however, you can regard it as what’s represented in SQL by a SELECT expression with a GROUP BY clause. Now, the foregoing SQL expressions don’t have a GROUP BY clause—but they’re defined to be shorthand for the following, which do (and do therefore represent summarizations as claimed):

     SELECT COUNT ( * ) AS X
     FROM   S
     GROUP  BY ( )

     SELECT COUNT ( DISTINCT STATUS ) AS Y
     FROM   S
     GROUP  BY ( )

Note: In case these expressions look strange to you, I should explain that SQL does in fact allow both (a) GROUP BY clauses with an empty operand commalist and (b) GROUP BY clauses with the operand commalist enclosed in parentheses. What’s more, specifying a GROUP BY clause with an empty operand commalist (with or without parentheses) is equivalent to omitting the GROUP BY clause entirely. Here’s why:

So SQL does support summarization—but it doesn’t support aggregation as such. Sadly, the two concepts are often confused, and perhaps you can begin to see why. What’s more, the picture is confused still further by the fact that, in SQL, it’s common in practice for the table that results from an “aggregation” to be coerced to the single row it contains, or even doubly coerced to the single value that row contains: two separate errors (of judgment, if nothing else) thus compounding to make the SQL-style “aggregation” look more like a true aggregation after all! Such double coercion occurs in particular when the SELECT expression is enclosed in parentheses to form a scalar subquery, as in the following SQL assignments:

     SET X = ( SELECT COUNT ( * ) FROM S ) ;

     SET Y = ( SELECT COUNT ( DISTINCT STATUS ) FROM S ) ;

But assignment as such is far from being the only context in which such coercions occur (see Chapter 2 and Chapter 12).

Now, you might be thinking the discrepancy here is hardly earth shattering; you might even be thinking the result on the right above is somehow “better” than the one on the left. But (to state the obvious) there’s a logical difference between the two, and—to quote Wittgenstein again—all logical differences are big differences. Logical mistakes like the one under discussion are simply unacceptable in a system that’s meant to be solidly based on logic, as relational systems are. End of aside.

     X := SUM ( SP WHERE SNO = 'S5' , QTY ) ;