Receiving a Summary of Record Groups

The following information is necessary to receive summary records.

To transfer a summary record, do not leave this input area blank or specify an asterisk (*) (except when all the fields of the file specified at the prompt are specified in GROUP BY). The field names specified in SELECT (except for those specified in functions) must also have been specified in GROUP BY.

The functions and fields specified in SELECT return actual summary information for each group. Enter the field names and functions in SELECT in the order in which they are to be displayed.

Note: Null values are not included in the functions. When an entire value is null, the function output is set to null, except for COUNT. The COUNT output is 0.

The function format is as follows.

function (field-name)

This has the following meaning:

function

This is one of the following functions:

AVG: Transfers the average value of the specified fields for each record group. This function can be used only for numeric fields.
MIN: Transfers the minimum or lowest value of the specified fields for each record group.
MAX: Transfers the maximum or highest value of the specified fields for each record group.
SUM: Transfers the total value of the specified fields for each record group. This function can be used only for numeric fields.
COUNT: Transfers the total number of records that satisfy the WHERE condition for each record group. Specify COUNT(*).

field-name

This is the field name defined with the record format specified in FROM.

Each function returns one value for each record group. In SELECT, several functions can be specified. To do so, delimit the functions by commas, as follows:

SUPPNO, AVG(PRICE), MIN(PRICE), MAX(PRICE)

This indicates that the average, minimum, and maximum values for PRICE are calculated for each supplier after SUPPNO has been selected. A summary record is transferred according to the function selection. Specify SUPPNO in GROUP BY, because SUPPNO has not been used for the functions.