HP 3000 Manuals HP 3000 Manuals
Aggregate Functions
Aggregate functions specify a value computed using data described in an
argument. The argument, enclosed in parentheses, is an expression. The
value of the expression is computed using each row that satisfies a
SELECT statement. Aggregate functions can be specified in the select
list and the HAVING clause. Refer to the explanation of the SELECT
statement for more details.
Scope
SQL SELECT Statements
SQL Syntax
{ {Expression } }
{AVG ({[ALL ] ColumnName}) }
{ {[DISTINCT] } }
{ }
{ {Expression } }
{MAX ({[ALL ] ColumnName}) }
{ {[DISTINCT] } }
{ }
{ {Expression } }
{MIN ({[ALL ] ColumnName}) }
{ {[DISTINCT] } }
{ }
{ {Expression } }
{SUM ({[ALL ] ColumnName}) }
{ {[DISTINCT] } }
{ }
{ {* } }
{COUNT ({[ALL ] ColumnName})}
{ {[DISTINCT] } }
Parameters
Expression specifies a value to be obtained.
AVG computes the arithmetic mean of the values in the
argument; NULL values are ignored. AVG can be
applied only to numeric data types and to the
INTERVAL type. When applied to FLOAT or REAL,
the result is FLOAT. When applied to INTEGER or
SMALLINT, the result is INTEGER, and fractions
are discarded. When applied to DECIMAL, the
result is DECIMAL. When applied to INTERVAL, the
result is INTERVAL.
MAX finds the largest of the values in the argument;
NULL values are ignored. MAX can be applied to
numeric, alphanumeric, BINARY (not LONG), and
date/time data types; the result is the same data
type as that of the argument.
MIN finds the smallest of the values in the argument;
NULL values are ignored. MIN can be applied to
numeric, alphanumeric, BINARY (not LONG), and
date/time data types; the result is the same data
type as that of the argument.
SUM finds the total of all values in the argument.
NULL values are ignored. SUM can be applied to
numeric data types and INTERVAL only. When
applied to FLOAT or REAL, the result is FLOAT.
When applied to INTEGER or SMALLINT, the result
is INTEGER. When applied to DECIMAL, the result
is DECIMAL. When applied to INTERVAL, the result
is INTERVAL.
COUNT * counts all rows in all columns, including rows
containing NULL values. The result is INTEGER.
COUNT ColumnName counts all rows in a specific column; rows
containing NULL values are not counted. The data
type of the column cannot be LONG BINARY or LONG
VARBINARY. The result is INTEGER.
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.
Description
* If an aggregate function is computed over an empty, ungrouped
table, results are as follows:
* COUNT returns 1; SQLCODE equals 0.
* AVG, SUM, MAX, and MIN return NULL; SQLCODE equals 0.
* If an aggregate function is computed over an empty group or an
empty grouped table, all aggregate functions return no row at all.
* Refer to the "Data Types" chapter for information on truncation
and type conversion that may occur during the evaluation of
aggregate functions.
* Refer to the "Data Types" chapter for information on the resulting
precision and scale of aggregate functions involving DECIMAL
arguments.
* A warning message is returned if a NULL is removed from the
computation of an aggregate function.
Example
The average price of each part with more than five rows in table
PurchDB.SupplyPrice is calculated.
SELECT PartNumber, AVG(UnitPrice)
FROM PurchDB.SupplyPrice
GROUP BY PartNumber
HAVING COUNT * > 5
[REV BEG]