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.
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. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |