This average does not include rows where the numeric-expression is the NULL value.
Syntax 2 represents usage as a window function in a SELECT statement. As such, elements of window-spec can be specified either in the function syntax (inline), or in conjunction with a WINDOW clause in the SELECT statement.
See the window-spec definition provided in WINDOW clause.
For more information about using window functions in SELECT statements, including working examples, see Window functions.
Syntax 1 is a core feature of the SQL/2008 standard, while Syntax 2 comprises part of optional SQL/2008 language feature
T611, "Basic OLAP operations". The ability to specify DISTINCT over an expression that is not a column reference comprises
part of optional SQL language feature F561, "Full value expressions". SQL Anywhere also supports SQL/2008 language feature
F441, "Extended set function support", which permits operands of aggregate functions to be arbitrary expressions possibly
including outer references to expressions in other query blocks that are not column references.
SQL Anywhere does not support optional SQL/2008 feature F442, "Mixed column references in set functions". SQL Anywhere does
not permit the arguments of an aggregate function to include both a column reference from the query block containing the AVG
function, combined with an outer reference.
The following statement returns the value 49988.623200.
SELECT AVG( Salary ) FROM Employees;
The following statement returns the average product price from the Products table:
SELECT AVG( DISTINCT UnitPrice ) FROM Products;
The following statement returns an error with SQLSTATE 42W68 because the arguments of AVG contain both a quantified expression
from the subquery, and an outer reference (p.Quantity) from the outer SELECT block:
select * from Products as p
where p.Quantity > ( select avg( 0.5 * p.Quantity + 0.5 * s.Quantity )
from SalesOrderItems as s
where s.ProductID = p.ProductID )