Summarizing query results using aggregate functions

Aggregate functions display summaries of the values in specified columns. You can also use the GROUP BY clause, HAVING clause, and ORDER BY clause to group and sort the results of queries using aggregate functions, and the UNION operator to combine the results of queries.

When an ORDER BY clause contains constants, they are interpreted by the optimizer and then replaced by an equivalent ORDER BY clause. For example, the optimizer interprets ORDER BY 'a' as ORDER BY expression.

A query block containing more than one aggregate function with valid ORDER BY clauses can be executed if the ORDER BY clauses can be logically combined into a single ORDER BY clause. For example, the following clauses:

ORDER BY expression1, 'a', expression2
ORDER BY expression1, 'b', expression2, 'c', expression3

are subsumed by the clause:

ORDER BY expression1, expression2, expression3

You can apply aggregate functions to all the rows in a table, to a subset of the table specified by a WHERE clause, or to one or more groups of rows in the table. From each set of rows to which an aggregate function is applied, SQL Anywhere generates a single value.

The following are some of the supported aggregate functions:

  • AVG( expression )   The mean of the supplied expression over the returned rows.

  • COUNT( expression )   The number of rows in the supplied group where the expression is not NULL.

  • COUNT( * )   The number of rows in each group.

  • LIST( string-expr )   A string containing a comma-separated list composed of all the values for string-expr in each group of rows.

  • MAX( expression )   The maximum value of the expression, over the returned rows.

  • MIN( expression )   The minimum value of the expression, over the returned rows.

  • STDDEV( expression )   The standard deviation of the expression, over the returned rows.

  • SUM( expression )   The sum of the expression, over the returned rows.

  • VARIANCE( expression )   The variance of the expression, over the returned rows.

For a complete list of aggregate functions, see Aggregate functions.

You can use the optional keyword DISTINCT with AVG, SUM, LIST, and COUNT to eliminate duplicate values before the aggregate function is applied.

The expression to which the syntax statement refers is usually a column name. It can also be a more general expression.

For example, with this statement you can find what the average price of all products would be if one dollar were added to each price:

SELECT AVG ( UnitPrice + 1 )
FROM Products;
 Example

Where you can use aggregate functions
Aggregate functions and data types
Using COUNT( * )
Using aggregate functions with DISTINCT
Aggregate functions and NULL