Aggregate functions summarize data over a group of rows from the database. The groups are formed using the GROUP BY clause of the SELECT statement.
Simple aggregate functions, such as SUM(), MIN(), MAX(), AVG() and COUNT() are allowed only in the select list and in the HAVING and ORDER BY clauses of a SELECT statement. These functions summarize data over a group of rows from the database. Groups are formed using the GROUP BY clause of the SELECT statement.
A new class of aggregate functions, called window functions, provides moving averages and cumulative measures that compute answers to queries such as, “What is the quarterly moving average of the Dow Jones Industrial average,” or “List all employees and their cumulative salaries for each department.”
Simple aggregate functions, such as AVG(), COUNT(), MAX(), MIN(), and SUM() summarize data over a group of rows from the database. The groups are formed using the GROUP BY clause of the SELECT statement.
Newer statistical aggregate functions that take one argument include STDDEV(), STDDEV_SAMP(), STDDEV_POP(), VARIANCE(), VAR_SAMP(), and VAR_POP().
Both the simple and newer categories of aggregates can be used as a windowing function that incorporates a <window clause> in a SQL query specification (a window) that conceptually creates a moving window over a result set as it is processed. See “Analytical functions”.
Table 5-1 lists the aggregate functions and their parameters.
Aggregate function |
Parameters |
|---|---|
AVG |
( [ DISTINCT] { column-name | numeric-expr } ) |
COUNT |
( * ) |
COUNT |
( [ DISTINCT] { column-name | numeric-expr } ) |
MAX |
( [ DISTINCT] { column-name | numeric-expr } ) |
MIN |
( [ DISTINCT] { column-name | numeric-expr } ) |
STDDEV |
( [ ALL ] expression ) |
SUM |
( [ DISTINCT] { column-name | numeric-expr } ) |
VARIANCE |
( [ ALL ] expression ) |
The aggregate functions AVG, SUM, STDDEV, and VARIANCE do not support the binary data types (BINARY and VARBINARY).
See the individual analytical function descriptions in this chapter for specific details on the use of each function.
For more information about using the OLAP functions, see Chapter 4, “Using OLAP” in the Sybase IQ Performance and Tuning Guide.
