SQL-J Language Reference
Page 84 of 121

SUM

SUM is an aggregate expression that evaluates the sum of the expression over a set of rows (see Aggregates (Set Functions)). SUM is allowed only on expressions that evaluate to numeric data types.

Syntax

SUM ( [ DISTINCT | ALL ] Expression )

The DISTINCT and ALL qualifiers eliminate or retain duplicates. ALL is assumed if neither ALL nor DISTINCT is specified. For example, if a column contains the values 1, 1, 1, 1, and 2, SUM(col) returns a greater value than COUNT(DISTINCT col).

Only one DISTINCT aggregate expression per SelectExpression is allowed. For example, the following query is not allowed:

SELECT AVG (DISTINCT flying_time), SUM (DISTINCT miles)
FROM Flights

The Expression can contain multiple column references or expressions, but it cannot contain another aggregate or subquery. It must evaluate to a built-in numeric data type or to a Java data type that is automatically mapped to a built-in numeric data type. You can therefore call methods that evaluate to built-in data types. (For example, a method that returns a java.lang.Integer or int evaluates to an INTEGER.) If an expression evaluates to NULL, the aggregate skips that value.

The resulting data type is the same as the expression on which it operates (it may overflow).

SUM Examples

-- find all the rooms booked
SELECT SUM (rooms_taken)
FROM HotelAvailability

-- use SUM on a method invocation
SELECT SUM (fixed_rate.doubleValue())
FROM FlightBookings

-- use SUM on multiple column references
-- (find the total value of all seats purchased)
SELECT SUM(fixed_rate.doubleValue() * number_seats)
FROM FlightBookings