1.1.1

Built-ins are SQL92Identifiers and are case-insensitive.
Standard Built-in Functions
SQLFire supports many Apache Derby functions.
Aggregates (set functions)
This section describes aggregates (also described as set functions in ANSI SQL-92 and as column functions in
some database literature). They provide a means of evaluating an expression over a set of rows. Whereas the
other built-in functions operate on a single expression, aggregates operate on a set of values and reduce them to
a single scalar value. Built-in aggregates can calculate the minimum, maximum, sum, count, and average of an
expression over a set of values as well as count rows.
The built-in aggregates can operate on the data types shown in the following table.
Table 7: Permitted data types for built-in aggregates
Permitted Data TypesFunction Name
All typesCOUNT
Numeric built-in data typesMIN
Numeric built-in data typesMAX
Numeric built-in data typesAVG
Numeric built-in data typesSUM
Aggregates are permitted only in the following:
• A SelectItem in a SelectExpression on page 541.
• A HAVING Clause on page 535.
• An ORDER BY Clause on page 536 (using an alias name) if the aggregate appears in the result of the relevant
query block. That is, an alias for an aggregate is permitted in an ORDER BY Clause on page 536 if and only
if the aggregate appears in a SelectItem in a SelectExpression on page 541.
All expressions in SelectItems in the SelectExpression on page 541 must be either aggregates or grouped
columns (see GROUP BY Clause on page 534). (The same is true if there is a HAVING clause without a GROUP
BY clause.) This is because the ResultSet of a SelectExpression on page 541 must be either a scalar (single
value) or a vector (multiple values), but not a mixture of both. (Aggregates evaluate to a scalar value, and the
reference to a column can evaluate to a vector.) For example, the following query mixes scalar and vector values
and thus is not valid:
-- not valid
SELECT MIN(flying_time), flight_id
FROM Flights
Aggregates are not allowed on outer references (correlations). This means that if a subquery contains an aggregate,
that aggregate cannot evaluate an expression that includes a reference to a column in the outer query block. For
example, the following query is not valid because SUM operates on a column from the outer query:
SELECT c1
FROM t1
GROUP BY c1
HAVING c2 >
(SELECT t2.x
vFabric SQLFire User's Guide554
vFabric SQLFire Reference