Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Summarizing, Grouping, and Sorting Query Results

Summarizing, Grouping, and Sorting Query Results Next Page

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.

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 among the available aggregate functions:

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

The following query calculates the total payroll from the annual salaries in the Employees table:

SELECT SUM(Salary)
FROM Employees

To use aggregate functions, you must give the function name followed by an expression on whose values it will operate. The expression, which is the Salary column in this example, is the function's argument and must be specified inside parentheses.


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