Sorting and Aggregating Report Columns

In this section:

How to:

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.
Top of page
x
Syntax: How to Sort and Aggregate a Report Column
[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:

RANKED

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.

n

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.

display_field

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.

Example: Sorting and Aggregating Report Columns

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
Example: Sorting, Aggregating, and Ranking Report Columns

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
Example: Sorting and Aggregating Report Columns With COMPUTE

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
Top of page
x
Restricting the Number of Columns in a Report

The maximum number of report columns created using the ACROSS phrase in a report is 255. The actual number of columns created by ACROSS depends on the number of:

Note: If you exceed the maximum number of ACROSS columns, a message displays.

Other factors that affect the number of ACROSS columns allowed in a report include the sizes 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