In this section: |
How to: |
Reference: |
Using the BY TOTAL phrase, you can apply aggregation and sorting simultaneously to numeric columns in your report in one pass of the data. For BY TOTAL to work correctly, you must have an aggregating display command such as SUM. A non-aggregating display command, such as PRINT, simply retrieves the data without aggregating it. Records are sorted in either ascending or descending sequence, based on your query. Ascending order is the default.
You can also use the BY TOTAL phrase to sort based on temporary values calculated by the COMPUTE command.
Note: On z/OS, the sort on the aggregated value is calculated using an external sort package, even if EXTSORT = OFF.
[RANKED] BY [HIGHEST|LOWEST [n] ] TOTAL {display_field|COMPUTE name/format=expression;}
or
[RANKED] BY TOTAL {[HIGHEST|LOWEST [n] ] display_field|COMPUTE name/format=expression;}
where:
Adds a column to the report in which a rank number is assigned to each aggregated sort value in the report output. If multiple rows have the same ranking, the rank number only appears in the first row.
Is the number of sort field values you wish to display in the report. If n is omitted, all values of the calculated sort field are displayed. The default order is from lowest to highest.
Can be a field name, a field name preceded by an operator (that is, prefixoperator.fieldname), or a calculated value.
A BY TOTAL field is treated as a display field when the internal matrix is created. After the matrix is created, the output lines are aggregated and re-sorted based on all of the sort fields.
In this example, the average of the wholesale prices is calculated and used as a sort field, and the highest two are displayed.
TABLE FILE MOVIES SUM WHOLESALEPR CNT.WHOLESALEPR BY CATEGORY BY HIGHEST 2 TOTAL AVE.WHOLESALEPR AS 'AVE.WHOLESALEPR' BY RATING WHERE CATEGORY EQ 'CLASSIC' OR 'FOREIGN' OR 'MUSICALS' END
The output is:
WHOLESALEPR CATEGORY AVE.WHOLESALEPR RATING WHOLESALEPR COUNT -------- --------------- ------ ----------- ----------- CLASSIC 40.99 G 40.99 1 16.08 NR 160.80 10 FOREIGN 31.00 PG 62.00 2 23.66 R 70.99 3 MUSICALS 15.00 G 15.00 1 13.99 PG 13.99 1 R 13.99 1
In this example, the average of the wholesale prices is calculated and used as a sort field, and the highest two are displayed and ranked.
TABLE FILE MOVIES SUM WHOLESALEPR CNT.WHOLESALEPR BY CATEGORY RANKED BY HIGHEST 2 TOTAL AVE.WHOLESALEPR AS 'AVE.WHOLESALEPR' BY RATING WHERE CATEGORY EQ 'CLASSIC' OR 'FOREIGN' OR 'MUSICALS' END
The output is:
WHOLESALEPR CATEGORY RANK AVE.WHOLESALEPR RATING WHOLESALEPR COUNT -------- ---- --------------- ------ ----------- ----------- CLASSIC 1 40.99 G 40.99 1 2 16.08 NR 160.80 10 FOREIGN 1 31.00 PG 62.00 2 2 23.66 R 70.99 3 MUSICALS 1 15.00 G 15.00 1 2 13.99 PG 13.99 1 R 13.99 1
In this example, the monthly salary is calculated using a COMPUTE within a sort field. The two highest monthly salaries are displayed.
TABLE FILE EMPLOYEE SUM SALARY CNT.SALARY BY DEPARTMENT BY HIGHEST 2 TOTAL COMPUTE MONTHLY_SALARY/D12.2M=SALARY/12; AS 'HIGHEST,MONTHLY,SALARIES' BY CURR_JOBCODE END
The output is:
HIGHEST MONTHLY SALARY DEPARTMENT SALARIES CURR_JOBCODE SALARY COUNT ---------- -------- ------------ ------ ------ MIS $4,403.08 A17 $52,837.00 2 $3,019.17 B03 $36,230.00 2 PRODUCTION $4,273.50 A15 $51,282.00 2 $2,591.67 B02 $31,100.00 2
The following request creates the calculated value PROFIT and uses it in the BY TOTAL phrase. The request also has an ACROSS RATING phrase.
TABLE FILE MOVIES SUM LISTPR WHOLESALEPR COMPUTE PROFIT = LISTPR - WHOLESALEPR; BY CATEGORY BY TOTAL PROFIT ACROSS RATING WHERE RATING NE 'NR' OR 'R' ON TABLE SET PAGE NOLEAD ON TABLE SET STYLE *GRID = OFF,$ ENDSTYLE END
The output is shown in the following image.
The number of columns created by ACROSS depends on the number of:
Row totals, BY columns, SUBHEADs, SUBFOOTs, and fields used in headings and footings do not count in calculating the number of ACROSS columns.
|
Information Builders |