Aggregate Functions
GROUP BY works in conjunction with SQL aggregate functions (AVG(), SUM(), COUNT(), MIN(), and MAX(). These functions perform operations for each record in an individual group or produce a summary value for each set.
Aggregate Function Syntax
The following identifies the general aggregate function syntax:
AGG(<DISTINCT  ALL (default)> colname)
Parameter 
Description 
AGG 
Name of an aggregate function 
colname 
Column from a table. If DISTINCT is specified, then only distinct colname values will be considered for aggregation. If ALL is specified, then all values will be considered for aggregation. 
The following identifies the syntax for the (AVG(), SUM(), COUNT(), MIN(), and MAX() aggregate functions:
Function 
Syntax 
Returns 
SUM() 
SUM(<DISTINCT  ALL> colname 
Summation of all colname numeric values for each group. 
MIN() 
MIN(<DISTINCT  ALL> colname) 
Lowest colname numeric or string values for each group. 
MAX() 
MAX(<DISTINCT  ALL> colname) 
Highest colname numeric or string values for each group. 
AVG() 
AVG(<DISTINCT  ALL> colname) 
Arithmetic mean for numeric colname values for each group. 
COUNT() 
COUNT(*) 
Number of records in each group. 

COUNT(colname) 
Count of distinct colname items in each group. 
Example
In the following example, the average SALES_LY will be determined from the entire CUSTOMER table. Since GROUP BY is not specified, the result set is only be a single row:
select AVG(sales_ly) from customer
In the following example, the average sales grouped by salesperson, and a row is shown for each salesperson:
select AVG(sales_ly), salesperson from customer group by salesperson