PURPOSE: SUMMARIZE is an equated command which performs cumulative calculations on the fields in a Data Set (DS) at the command level or in a Command Module (CM) object. The SUMMARIZE utility has arithmetic functions like the inter-record functions used in a Process Module (PM). The output data can be:
Displayed at the terminal
Written or appended to a System File (SF)
Written or appended to a DS
SUMMARIZE [ON \\field\\]
[IF; UNLESS condition]
[WHEN \\ /field_list/ [operator] search_key\\]
[\USE\ DOMAIN domain]
[SHOW/ING/ \\ item [@"format"] \\ [APPEND/ING/] SAVE /ON/ sf_name]]
[SET \\ item TO ds_field \\ [APPEND/ING/] TO ds_name]
[DEFINE PM pm_name]
[VERSION]
|
ON \\field\\ |
specifies the field names that control the summary breaks. A change in value in one of these fields causes the arithmetic functions (SUM, MIN, MAX, AVG) to be performed, if used. The arithmetic functions are described in the NOTES subsection. Up to ten fields can be specified in the ON clause. This clause also specifies the sequence of the records to be summarized. The records in the DS must be sorted according to the fields specified in the ON clause (logically through a Data Index (DI) or physically). SUMMARIZE does NOT CHECK the sequence of the records. |
||
|
IF; UNLESS condition |
specifies the records that qualify for the SUMMARIZE operation. If this clause is omitted, all records are considered for the C operation. |
||
|
WHEN \\ /field_list/ [operator] search_key\\ |
specifies the key values of records to retrieve. If this clause is specified, a DI must be declared. |
||
|
\USE\ DOMAIN domain |
specifies a domain to use in a declared DI. |
||
|
SHOW/ING/ \\item [@"format"]\\ |
specifies items to be displayed. Each item must be separated by a comma. An "item" may be: |
||
|
|
Item Field name System Field name Constant Literal Arithmetic expression Arithmetic function Arithmetic expression of functions |
EXAMPLE PUB_CODE, PRICE @DATE 123 "***" QTY*PRICE SUM QTY MAX(QTY)-MIN(QTY) |
|
|
|
Blank spaces may also be specified (such as 1S, 2B). The arithmetic functions (SUM, MIN, MAX, AVG) are described in the NOTES subsection. |
||
|
APPEND/ING/ |
cause output to be added to the data already in the System File (SF). If APPEND/ING/ is not specified, the SUMMARIZE output replaces existing data in the SF. |
||
|
SAVE/ON/ sf_name |
specifies to write the items of the SHOW/ING/ clause to the named SF. |
||
|
SET \\ item TO ds_field \\ |
specifies items to be stored in the records created. The ds_field name is the name of the field in the output record that is to contain the specified item. Each item subclause must be separated by a comma. |
||
|
|
Item Field name System Field name Constant Literal Arithmetic expression Arithmetic function Arithmetic expression of functions |
EXAMPLE QTY TO NEWQTY @DATE TO ORDER_DATE 1238 TO PUB_CODE "***" TO NOTES QTY*PRICE TO SALES SUM YTD_SALES MAX(QTY)-MIN(QTY) TO QRANGE |
|
|
|
The arithmetic functions (SUM, MIN, MAX, AVG) are described later in the NOTES subsection. |
||
|
[APPEND/ING/] TO ds_name |
causes the output records to be added to the data already in the DS. If APPEND/ING/ is not specified, the SUMMARIZE output replaces existing data in the DS. A record is created for each summary group. |
||
|
DEFINE PM pm_name |
SAVEs the PM that is generated when SUMMARIZE is invoked. The user specifies the PM name. The PM name must not already exist. Later, the PM can be run as part of an application. This saves the time it takes to generate the PM each time the SUMMARIZE is executed. The generated PM can also be modified. |
||
|
VERSION |
displays the current version of the utility. |
||
*USE DBL BOOKS
*LIST SD SALES2
00100 STORE_CODE, CHAR,4
00110 CITY, CHAR,20
00120 PAY_TERMS, CHAR,12
00130 QTY, INT,6
*LIST DI SALES2
00100 INDEX TYPE IS RAM
00110 INDEX TO DS SALES2
00120 DOMAIN STORE_CODE ON STORE_CODE
00130 DOMAIN CITY ON CITY
*USE DS SALES2 DI SALES2
*EXTRACT
5777 ATLANTA Net 30 45
7189 DETROIT Net 60 100
6442 BOSTON Invoice 255
3333 NEW YORK Net 30 50
2225 CHICAGO Net 30 75
8616 CHICAGO Net 60 25
4860 ATLANTA Invoice 200
1954 BOSTON Net 30 60
9091 NEW YORK Net 60 40
0508 DETROIT Net 30 30
EXAMPLE 1 - SUMMARIZE on All Records
*SUMMARIZE SHOW "TOTAL ORDER:", 3S, SUM QTY@"5$", @CR, &
"MINIMUM ORDER:", 1S, MIN QTY@"3$", @CR, &
"MAXIMUM ORDER:", 1S, MAX QTY@"3$", @CR, &
"AVERAGE ORDER:", 1S, AVG QTY@"3$.D"
TOTAL ORDER: 880
MINIMUM ORDER: 25
MAXIMUM ORDER: 255
AVERAGE ORDER: 88.0
EXAMPLE 2 - SUMMARIZE on One Key Field
*SUMMARIZE ON PAY_TERM SHOW PAY_TERM, 2S, SUM QTY@"5$", &
1S, MIN QTY@"3$", 1S, MAX SALES_AMT@"3$", 1S, AVG QTY@"3$.D"
Net 30 260 30 75 52.0
Net 60 165 25 100 55.0
Invoice 455 200 255 227.5
EXAMPLE 3 - SUMMARIZE with IF Condition
*SUMMARIZE IF PAY_TERM = 'Net 60' ON SHOW PAY_TERM, 1S,SUM &
QTY@"5$", 1S, AVG QTY@"3$.D"
Net 60 165 55.0
EXAMPLE 4 - Write SUMMARIZE Output to SF
*SUMMARIZE ON PAY_TERM SHOW PAY_TERM, 2S, SUM QTY@"5$",&
1S, MIN QTY@"3$", 1S, MAX SALES_AMT@"3$",1S, AVG QTY@"3$.D" &
SAVE ON SUM.REP
*LIST SF SUM.REP
(LISTing the SF report will reproduce the results in EXAMPLE 2.)
EXAMPLE 5 - APPEND SUMMARIZE Output to SF
*SUMMARIZE IF PAY_TERM = 'Net 60' ON SHOW PAY_TERM, 1S,&
SUM QTY@"5$", 1S, AVG QTY@"3$.D" APPEND SAVE ON SUM.REP
*LIST SF SUM.REP
Net 30 260 30 75 52.0
Net 60 165 25 100 55.0
Invoice 455 200 255 227.5
Net 60 165 55.0
EXAMPLE 6 - Write SUMMARIZE Output to DS
*LIST SD SALESTOT
00120 TOT_QTY, INT,6
00130 TOT_TERMS, CHAR,12
*SUMMARIZE ON PAY_TERMS SET PAY_TERMS TO TOT_TERMS, SUM QTY &
TO TOT_QTY TO SALESTOT
*USE DS SALESTOT
*EXTRACT
Net 30 260
Net 60 165
Invoice 455
EXAMPLE 7 - APPEND SUMMARIZE Output to DS
*USE DS SALES2 DI SALES2
*SUMMARIZE ON PAY_TERMS SET PAY_TERMS TO TOT_TERMS, APPEND TO &
SALESTOT
*USE DS SALESTOT
*EXTRACT
Net 30 260
Net 60 165
Invoice 455
Net 30
Net 60
Invoice
EXAMPLE 8 - Write SUMMARIZE Output to SF and DS
*USE DS SALES2 DI SALES2
*SUMMARIZE ON PAY_TERMS SHOW PAY_TERMS, 2S, SUM QTY SAVE ON &
UM.REP SET PAY_TERMS TO TOT_TERMS TO SALESTOT
*LIST SF SUM.REP
Net 30 260
Net 60 165
Invoice 455
*USE DS SALESTOT
*EXTRACT
Net 30
Net 60
Invoice
EXAMPLE 9 - Retrieval with WHEN Clause
*USE DS SALES2 DI SALES2
*SUMMARIZE ON PAY_TERMS WHEN PAY_TERMS = "NET 30","NET 60" SHOW &
PAY_TERMS, 2S, SUM QTY@"5$", 1S, MIN QTY@"3$", &
1S, MAX QTY@"3$", 1S, AVG QTY@"3$.D"
Net 30 260 30 75 52.0
Net 60 165 25 100 55.0
EXAMPLE 10 - Retrieval with WHEN Clause and DOMAIN Clause
*SUMMARIZE ON CITY WHEN CITY = 'CHICAGO' DOMAIN CITY &
SHOW CITY, 1S, SUM QTY@"5$"
CHICAGO 100
EXAMPLE 11 - Retrieval with WHEN Clause (Key Range) and DOMAIN Clause
*SUMMARIZE ON CITY WHEN CITY = 'ATLANTA' THRU 'CHICAGO' &
SHOW CITY, 1S, SUM QTY@"5$", 1S, MIN QTY@"3$", &
1S, MAX QTY@"3$", 1S, AVG QTY@"3$.D" DOMAIN CITY
ATLANTA 245 45 200 122.5
BOSTON 315 60 255 157.5
CHICAGO 100 25 75 50.0
EXAMPLE 12 - Retrieval with WHEN Clause (Multiple Keys and Key Values)
*SUMMARIZE ON CITY WHEN CITY;PAY_TERMS = "ATLANTA"; "Net 30",&
"CHICAGO"; "Net 60" SHOW CITY,2S,PAY_TERMS,1S,SUM QTY "3$" &
DOMAIN CITY
ATLANTA Net 30 45
CHICAGO Net 60 25
EXAMPLE 13 - SORT with SUMMARIZE
*SORT ON CITY,PAY_TERMS
*SUMMARIZE ON CITY,PAY_TERMS SHOW CITY, 1S, PAYTERMS, 2S, SUM &
QTY@"5$"
ATLANTA Invoice 200
ATLANTA Net 30 45
BOSTON Invoice 255
BOSTON Net 30 60
CHICAGO Net 30 75
CHICAGO Net 60 25
DETROIT Net 30 30
DETROIT Net 60 100
NEW YORK Net 30 50
NEW YORK Net 60 40
EXAMPLE 14 - Creating a PM
*FORM DI SALES2L
*USE DI SALES2
*SUMMARIZE ON CITY WHEN CITY = "ATLANTA","NEW YORK" SHOW "CITY:",&
1S, CITY, 2S, "TOTAL QTY:", 3S, SUM QTY@"5$",&
@CR, 18S, "AVERAGE QTY:", 1S, AVG QTY@"3$.D",&
@CR, 18S, "MINIMUM QTY:", 1S, MIN QTY@"3$",&
@CR, 18S, "MAXIMUM QTY:", 1S, MAX QTY@"3$" DEFINE PM SUM_STORE
CITY: ATLANTA TOTAL QTY: 245
AVERAGE QTY: 122.5
MINIMUM QTY: 45
MAXIMUM QTY: 200
CITY: NEW YORK TOTAL QTY: 90
AVERAGE QTY: 45.0
MINIMUM QTY: 40
MAXIMUM QTY: 50
*LIST PM SUM_STORE
00005 !SUMMARIZE ON CITY WHEN CITY="ATLANTA","NEW YORK"SHOW&
"CITY:",1S,CITY,2S,"TOTAL QTY:",3S,SUM QTY@"5$",&
@CR,18S,"AVERAGE QTY:",1S, AVG QTY@"3$.D",&
@CR,18S,"MINIMUM QTY:",1S, MIN QTY@"3$",&
@CR,18S,"MAXIMUM QTY:",1S, MAX QTY@"3$",@CR&
VIA PM SUM_STORE
00050 CONTROL SECTION
00100 RELATE DI SALES2 IN DBL BOOKS AS MASTER FOR INPUT
00250 RELATE TERMINAL WITH REPORT 1
00800 INITIAL SECTION
00810 0 TO @TOP_MARGIN, @BOTTOM_MARGIN
00820 TURN FILL OFF
00830 TURN PAGING OFF
02000 TOTALS SECTION
02010 ON CITY
02020 PRINT "CITY:",1S, CITY,2S, "TOTAL SALES:”'3S,SUM &
QTY@"5$",@CR,18S,"AVERAGE QTY:",1S,AVG QTY@"3$.D",@CR,18S, &
"MINIMUM QTY:",1S,MIN QTY"3$",@CR,18S,"MAXIMUM QTY:",1S,MAX &
QTY@"3$"
NOTES: The option clauses may be specified in any order.
Either the SHOW/ING/ clause or the "TO ds_name" clause must be specified in the SUMMARIZE command.
When writing to a new DS, the Schema Definition (SD) and DS must be defined and created before executing the SUMMARIZE command. The SD can be different from the DS being summarized.
The SUMMARIZE command has four arithmetic functions that can be used in the SET clause or the SHOW/ING/ clause:
FUNCTION MEANING
SUM Total of all values for a summary group
MIN Minimum value for a summary group
MAX Maximum value for a summary group
AVG Average of values for a summary group
Each time a field that is specified in the ON clause changes value, the field specified with the SUM, MIN, MAX, or AVG function are clear for the next summary group. The operand of an arithmetic function can be a DS field or an arithmetic expression. An arithmetic expression must be enclosed in parentheses. See the explanations for the SET clause and the SHOW/ING/ clauses for examples.
Field specified with OCCURS clauses cannot be used in the command.