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

{ 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

Aggregate Functions

Technical documentation

» Table of Contents

» Index

Scope

SQL Syntax

Parameters

Description

Example