ALLBASE/SQL Reference Manual (36216-90216)

Chapter 8 237
Expressions
Aggregate Functions
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