Complex data analysis often requires multiple levels of aggregation. Window partitioning and ordering, in addition to, or instead of, a GROUP BY clause, offers you considerable flexibility in the composition of complex SQL queries. For example, by combining a window construct with a simple aggregate function, you can compute values such as moving average, moving sum, moving minimum or maximum, and cumulative sum.
Following are the basic aggregate functions in SQL Anywhere:
SUM function Returns the total of the specified expression for each group of rows.
AVG function Returns the average of a numeric expression or of a set unique values for a set of rows.
MAX function Returns the maximum expression value found in each group of rows.
MIN function Returns the minimum expression value found in each group of rows.
MEDIAN function Returns the median of a numeric expression for a set of rows.
FIRST_VALUE function Returns values from the first row of a window. This function requires a window specification.
LAST_VALUE function Returns values from the last row of a window. This function requires a window specification.
COUNT function Returns the number of rows that qualify for the specified expression.
SUM function example
AVG function example
MAX function example
FIRST_VALUE and LAST_VALUE function examples
Discuss this page in DocCommentXchange.

Copyright © 2010, iAnywhere Solutions, Inc.  SQL Anywhere 12.0.0 