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