Skip Headers
Oracle® TimesTen In-Memory Database SQL Reference
Release 11.2.1

Part Number E13070-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

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.
COUNT * 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

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

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