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:

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.

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.