Aggregate functions

Aggregate functions specify a value computed with data from a set of rows described in an argument. The argument, enclosed in parentheses, is an expression.

Aggregate functions can be specified in the select list or the HAVING clause. See "INSERT...SELECT" for more information. The value of the expression is computed using each row that satisfies the WHERE clause.

SQL syntax

{AVG   ({Expression | [ALL | DISTINCT] ColumnName})
 MAX   ({Expression | [ALL | DISTINCT] ColumnName | ROWID})
 MIN   ({Expression | [ALL | DISTINCT] ColumnName | ROWID})
 SUM   ({Expression | [ALL | DISTINCT] ColumnName})
 COUNT ({ * | [ALL | DISTINCT] ColumnName | ROWID})
}

Component	Description
`Expression`	Specifies an argument for the aggregate function. The expression itself cannot be an aggregate function.
AVG	Computes the arithmetic mean of the values in the argument. NULL values are ignored. AVG can be applied only to numeric data types.
MAX	Finds the largest of the values in the argument (ASCII comparison for alphabetic types). NULL values are ignored. MAX can be applied to numeric, character, and BINARY data types.
MIN	Finds the smallest of the values in the argument (ASCII comparison for alphabetic types). NULL values are ignored. MIN can be applied to numeric, character, and BINARY data types.
SUM	Finds the total of all values in the argument. NULL values are ignored. SUM can be applied to numeric data types only.
C OUNT *	Counts all rows that satisfy the WHERE clause, including rows containing NULL values. The data type of the result is TT_INTEGER. For more information on the number of rows in a table, see the description for the NUMTUPS field in SYS.TABLES.
COUNT `ColumnName`	Counts all rows in a specific column. Rows containing NULL values are not counted. The data type of the result is TT_INTEGER. For more information on the number of rows in a table, see the description for the NUMTUPS field in SYS.TABLES.
ALL	Includes any duplicate rows in the argument of an aggregate function. If neither ALL nor DISTINCT is specified, ALL is assumed.
DISTINCT	Eliminates duplicate column values from the argument of an aggregate function. Can be specified for more than one column.

Description

If an aggregate function is computed over an empty table in which GROUP BY is not used, the results are as follows:
- COUNT returns 0.
- AVG, SUM, MAX, and MIN return NULL.
If an aggregate function is computed over an empty group or an empty grouped table (GROUP BY is used):
- COUNT returns nothing.
- AVG, SUM, MAX, and MIN return nothing.
For SUM:
- If the source is TT_TINYINT, TT_SMALLINT, or TT_INTEGER, the result data type is TT_INTEGER.
- If the source is NUMBER, then the result data type is NUMBER with undefined scale and precision.
- If the source is TT_DECIMAL, then the result data type is TT_DECIMAL with maximum precision.
- For all other data types, the result data type is the same as the source.
For MAX and MIN:
- The result data type is the same as the source.
For AVG:
- AVG is evaluated as SUM/COUNT. The result data type is derived using the rule that is applied for the DIV operator.

See Chapter 1, "Data Types" for information about:

Truncation and type conversion that may occur during the evaluation of aggregate functions.
Precision and scale of aggregate functions involving numeric arguments.
Control of the result type of an aggregate function.

Examples

Calculate the average salary for employees in the HR schema. Use CAST to cast the average as the data type of the column:

Command> SELECT CAST(AVG (salary) AS NUMBER (8,2)) FROM employees;
< 6461.68 >

Calculate the MAX salary for employees in the HR schema:

Command> SELECT MAX (salary) FROM employees;
< 24000 >
1 row found.

The example uses DESCRIBE to show the data type that is returned when using the SUM aggregate. The aggregates table is created and columns with different data types are defined:

Command> CREATE TABLE aggregates (col1 TT_TINYINT, col2 TT_SMALLINT, col3 TT_INTEGER, col4 TT_BIGINT, col5 NUMBER (4,2), 
col6 TT_DECIMAL (6,2), col7 BINARY_FLOAT, col8 BINARY_DOUBLE);
Command> DESCRIBE SELECT SUM (col1) FROM aggregates;
Prepared Statement:
  Columns:
    EXP                             TT_INTEGER
Command> DESCRIBE SELECT SUM (col2) FROM aggregates;
Prepared Statement:
Columns:
    EXP                             TT_INTEGER
Command> DESCRIBE SELECT SUM (col3) FROM aggregates;
Prepared Statement:
Columns:
    EXP                             TT_INTEGER
Command> DESCRIBE SELECT SUM (col4) FROM Aggregates;
Prepared Statement:
Columns:
    EXP                             TT_BIGINT
Command> DESCRIBE SELECT SUM (col5) FROM aggregates;
Prepared Statement:
Columns:
    EXP                             NUMBER
Command> DESCRIBE SELECT SUM (col6) FROM aggregates;
Prepared Statement:
Columns:
    EXP                             TT_DECIMAL (40,2)
Command> DESCRIBE SELECT SUM (col7) FROM aggregates;
Prepared Statement:
Columns:
    EXP                             BINARY_FLOAT
Command> DESCRIBE SELECT SUM (col8) FROM Aagregates;
Prepared Statement:
  Columns:
    EXP                             BINARY_DOUBLE