Sorting and Aggregating Report Columns

How to:

Sort and Aggregate a Report Column

Reference:

Usage Notes for BY TOTAL

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.

Syntax: How to Sort and Aggregate a Report Column

[RANKED] BY [HIGHEST|LOWEST [n] ] 
         TOTAL {display_field|COMPUTE name/format=expression;}

[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

Example: Sorting, Aggregating, and Ranking Report Columns

Example: Sorting and Aggregating Report Columns With COMPUTE

Example: Using BY TOTAL on a Calculated Value With an ACROSS Phrase

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.

	WebFOCUS