User's Guide
   PART 1. Working with Databases
     CHAPTER 5. Summarizing, Grouping, and Sorting Query Results       

Summarizing query results using aggregate functions

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, Adaptive Server Anywhere generates a single value.

Example 

  To calculate the total payroll, from the annual salaries in the employee table:

1. Enter the following query:

   SELECT SUM(salary)
   FROM employee

To use the aggregate functions, you must give the function name followed by the name of the column on whose values it will operate. The column name, which is the function's argument, is in parentheses.

The following aggregate functions are available:

• 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.

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

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 (unit_price + !)
FROM product 

  Where you can use aggregate functions

The aggregate functions can be used in a select list, as in the previous examples, or in the HAVING clause of a select statement that includes a GROUP BY clause.

     For information about the HAVING clause, see The HAVING clause: selecting groups of data.

You cannot use aggregate functions in a WHERE clause or in a JOIN condition. However, a SELECT statement with aggregate functions in its select list often includes a WHERE clause that restricts the rows to which the aggregate is applied.

If a SELECT statement includes a WHERE clause, but not a GROUP BY clause, an aggregate function produces a single value for the subset of rows that the WHERE clause specifies.

Whenever an aggregate function is used in a SELECT statement that does not include a GROUP BY clause, it produces a single value, called a scalar aggregate. This is true whether it is operating on all the rows in a table or on a subset of rows defined by a where clause.

You can use more than one aggregate function in the same select list, and produce more than one scalar aggregate in a single SELECT statement.

  Aggregate functions and data types

There are some aggregate functions that have meaning only for certain kinds of data. For example, you can use SUM and AVG with numeric columns only.

However, you can use MIN to find the lowest value—the one closest to the beginning of the alphabet—in a character type column:

SELECT MIN(last_lname) 
FROM  contact

  Using count (*)

The COUNT(*) function does not require an expression as an argument because, by definition, it does not use information about any particular column. The COUNT(*) function finds the total number of rows in a table. This statement finds the total number of employees:

SELECT COUNT(*) 
FROM employee

COUNT(*) returns the number of rows in the specified table without eliminating duplicates. It counts each row separately, including rows that contain NULL.

Like other aggregate functions, you can combine count(*) with other aggregates in the select list, with where clauses, and so on:

SELECT count(*), avg(unit_price) 
FROM product
WHERE  unit_price > 10 

count(*)	avg(unit_price)
5	18.200

  Using aggregate functions with DISTINCT

The DISTINCT keyword is optional with SUM, AVG, and COUNT. When you use DISTINCT, duplicate values are eliminated before calculating the sum, average, or count.

If you use DISTINCT, you cannot include an arithmetic expression in the argument. The argument must use a column name only.

For example, to find the number of different cities in which there are contacts, type:

SELECT count(DISTINCT city) 
FROM contact

count(distinct city)
16

  Aggregate functions and NULL

Any NULLS in the column on which the aggregate function is operating are ignored for the purposes of the function except COUNT(*), which includes them. If all the values in a column are NULL, COUNT(column_name) returns 0.

If no rows meet the conditions specified in the WHERE clause, COUNT returns a value of 0. The other functions all return NULL. Here are examples:

SELECT COUNT (DISTINCT name)
FROM product
WHERE unit_price > 50

count(DISTINCT name)
0

SELECT AVG(unit_price)
FROM product
WHERE unit_price > 50

AVG ( unit_price)
( NULL )