Aggregate Expressions

An aggregate expression uses an aggregate function to summarize selected database data. The built-in aggregate functions have the following syntax. 
(1)
Aggregate Expressions

1 
2.1 COUNT( * ) 
2.1 
2.2.1 AVG
2.2.1 COUNT
2.2.1 MAX
2.2.1 MIN
2.2.1 SUM
2.2.1 RANGE
2.2.1 STDEV
2.2.1 VARIANCE
2.1 (
2.2.1?  %Aggregate Scope
Qualifiers )
2.2.1 !  ALL  %Subset of Expression1 )
1   %User-Defined Aggregates2
Aggregate Scope Qualifiers

1  
2.1!  ALL
2.1 DISTINCT
2.1 UNIQUE
2.1 table .
2.1 alias .
2.1 view .
2.1 synonym .
1 column
Notes:
Element Description Restrictions Syntax
column Column to which aggregate function is applied See headings for individual keywords on pages that follow Identifier
alias, synonym, table, view Synonym, table, view, or alias that contains column Synonym and the table or view to which it points must exist Identifier

You cannot use an aggregate expression in a condition that is part of a WHERE clause unless you use the aggregate expression within a subquery. You cannot apply an aggregate function to a BYTE or TEXT column. For other general restrictions, see Subset of Expressions Valid in an Aggregate Expression.

An aggregate function returns one value for a set of queried rows. The following examples show aggregate functions in SELECT statements: 
SELECT SUM(total_price) FROM items WHERE order_num = 1013;

SELECT COUNT(*) FROM orders WHERE order_num = 1001;

SELECT MAX(LENGTH(fname) + LENGTH(lname)) FROM customer;

If you use an aggregate function and one or more columns in the projection list of the Projection clause, you must include all the column names that are not used as part of an aggregate or time expression in the GROUP BY clause.