SQL-J Language Reference
Page 61 of 121

AVG

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

Syntax

AVG ( [ DISTINCT | ALL ] Expression )

The DISTINCT qualifier eliminates duplicates. The ALL qualifier retains duplicates. ALL is the default value if neither ALL nor DISTINCT is specified. For example, if a column contains the values 1.0, 1.0, 1.0, 1.0, and 2.0, AVG(col) returns a smaller value than AVG(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 an SQL-92 numeric data type or to a Java data type that is automatically mapped to an SQL-92 numeric data type. You can therefore call methods that evaluate to SQL-92 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 will never overflow). The following query, for example, returns the INT 1 (which may not be what you would expect):

SELECT AVG(c1)
FROM (VALUES (1), (1), (1), (1), (2)) AS myTable (c1)

CAST the expression to another data type if you want more precision:

SELECT AVG(CAST (c1 AS DOUBLE PRECISION))
FROM (VALUES (1), (1), (1), (1), (2)) AS myTable (c1)

AVG Examples

-- find the average of column 1
SELECT AVG (col1)
FROM (VALUES (1.0, 2), (2.0, 3), (2.0, 3))
    AS virtualTable (col1, col2)

-- find the average flying time of
-- flights between 1000 and 1500 miles
SELECT AVG (flying_time)
FROM Flights
WHERE miles > 1000 AND miles < 1500

-- use AVG in the HAVING clause to filter the groups
SELECT AVG (flying_time), orig_airport
FROM Flights
GROUP BY orig_airport
HAVING AVG (flying_time) > 5.0

-- find the average of a method invocation
SELECT AVG (person.getAge()) AS AVERAGE_AGE
FROM People