|
|
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.
SQL SELECT Statements
{
AVG ({ Expression
[ALL | DISTINCT] ColumnName} )
MAX ({ Expression
[ALL | DISTINCT] ColumnName} )
MIN ({ Expression
[ALL | DISTINCT] ColumnName} )
SUM ({ Expression
[ALL | DISTINCT] ColumnName} )
COUNT ({ *
[ALL | DISTINCT] ColumnName} )
}
- 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.
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.
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
|