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.
function (field-name)
- 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.
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.