Aggregation usage codes

You use aggregation usage codes to summarize data in a column or replace data with a calculation.

The following table shows which aggregation usage codes are valid when used with different data types.

Table 1. Valid usage codes for data types
Data type	Valid usage codes
Numeric	AVG, COUNT, CPCT, CSUM, FIRST, LAST, MAX, MIN, PCT, STDEV, SUM, TCPCT, TPCT
Character, Date, Time, Timestamp, Timestamp with time zone	COUNT, FIRST, LAST, MAX, MIN

Restriction: LONG VARCHAR and LONG VARGRAPHIC columns cannot be aggregated. The only valid usage code for these data types is OMIT; you can also leave the USAGE field blank.

Summarizing data in a column

Reports:

Aggregation usage codes summarize the data in a column. The results of an aggregation can appear in the middle of the report as subtotals or at the end of the report as totals.

AVERAGE: Average of the values in the column
COUNT: Count of the values in the column
FIRST: First value in the column
LAST: Last value in the column
MAXIMUM: Maximum value in the column
MINIMUM: Minimum value in the column
STDEV: Standard deviation of the values in the column
SUM: Sum of the values in the column

When you use MAXIMUM and MINIMUM on character, date, time, timestamp, or graphic data, QMF™ uses an EBCDIC collating sequence to compare the data. To determine the maximum and minimum for numeric data, QMF uses algebraic comparisons. Nulls can be included in the result for MAX, MIN, FIRST, and LAST.

A date/time function applied to a DATE, TIME, TIMESTAMP, or TIMESTAMP WITH TIME ZONE value changes the data type of that value to numeric. Therefore, the resulting value can be aggregated.

The format of the result is determined by the edit code of the column, except for COUNT, STDEV, and percentage aggregations. COUNT can be applied to data of any type, but always produces an integer result; thus, its result is formatted with edit code K. STDEV, PCT, CPCT, TPCT, and TCPCT are formatted with edit code L.

Charts:

The information on reports for these usage codes is also true for charts.

AVERAGE, MAXIMUM, MINIMUM, STDEV, and SUM can all be useful in charting QMF data. Entries such as FIRST and LAST might not be useful in a chart format.

The following values are sent as null values to the ICU when you display a chart of the report:

Null values in a report
Data values too long for the width of the column
Undefined values
Arithmetic overflow values

Replacing a data value with a calculation

Reports:

The following codes refer to aggregations that replace each detail line value in a column with a calculation and show a final result of the aggregation at the end of the report. They can also appear in the middle of the report as subtotals.

CSUM

The cumulative sum for each value in a column.

PCT

The percentage each value is of the total:

In reports with BREAK or ACROSS usages, PCT shows what percentage each value in the break or across group is of the break or across total.
In all other reports, PCT shows the percentage each value in the column is of the column total.

CPCT

The cumulative percentage for each value in a column:

In reports with BREAK or ACROSS usages, CPCT shows the cumulative percentage of the break or across total for each value in the break or across group.
In all other reports, CPCT shows the cumulative percentage each value in the column is of the column total.

TPCT

The total percentage each value is of the column total:

In reports with BREAK or ACROSS usages, TPCT shows what percentage each value in the column is of the column total.
In all other reports, TPCT displays the column total.

TCPCT

The total cumulative percentage for each value in a column:

In reports with BREAK or ACROSS usages, TCPCT shows the cumulative percentage each value in the column is of the column total.
In all other reports, TCPCT displays the column total.

These aggregations work only on numeric data. Nulls in the column are not included in the result, but undefined values and numeric overflows are evaluated. The format of the result is determined by the edit code of the column.

Four versions of a report follow. The only difference is a result of the aggregation specified on the form for the salary column.

Report 1: SUM SALARY (total)

                                  SUM
           NAME       JOB        SALARY
           ---------  -----  ----------
           MOLINARE   MGR      22959.20
           LU         MGR      20010.00
           DANIELS    MGR      19260.25
           JONES      MGR      21234.00
                             ==========
                               83463.45

Report 2: CSUM SALARY (cumulative total)

                                   CSUM
            NAME       JOB        SALARY
            ---------  -----  ----------
            MOLINARE   MGR      22959.20
            LU         MGR      42969.20
            DANIELS    MGR      62229.45
            JONES      MGR      83463.45
                              ==========
                                83463.45

Report 3: PCT SALARY (percentage)

                                  PCT
           NAME       JOB        SALARY
           ---------  -----  ----------
           MOLINARE   MGR         27.51
           LU         MGR         23.97
           DANIELS    MGR         23.08
           JONES      MGR         25.44
                             ==========
                                 100.00

Report 4: CPCT SALARY (cumulative percentage)

                                  CPCT
           NAME       JOB        SALARY
           ---------  -----  ----------
           MOLINARE   MGR         27.51
           LU         MGR         51.48
           DANIELS    MGR         74.56
           JONES      MGR        100.00
                             ==========
                                 100.00

Two versions of the same report with a break follow. The first report uses PCT to show:

The percentage each salary is of its break group total
The percentage each break group is of the column total

                               PCT
        JOB    NAME           SALARY
        -----  ---------  ----------
        CLERK  JAMES           25.71
               KERMISCH        23.34
               NGAN            23.81
               SNEIDER         27.14
                          ----------
                       *       41.61
 
        MGR    HANES           52.95
               SANDERS         47.05
                          ----------
                       *       30.91
 
        SALES  PERNAL          52.41
               ROTHMAN         47.59
                          ----------
                       *       27.47
 
                          ==========
                              100.00

This report uses TPCT to show:

The percentage each salary is of the column total
Subtotals at the breaks

                               TPCT
        JOB    NAME           SALARY
        -----  ---------  ----------
        CLERK  JAMES           10.70
               KERMISCH         9.71
               NGAN             9.91
               SNEIDER         11.29
                          ----------
                       *       41.61
 
        MGR    HANES           16.37
               SANDERS         14.54
                          ----------
                       *       30.91
 
        SALES  PERNAL          14.40
               ROTHMAN         13.08
                          ----------
                       *       27.47
 
                          ==========
                              100.00

Whenever you use a percentage usage code (PCT, CPCT, TPCT, and TCPCT), QMF shows the total percentage as 100. However, occasionally the individual percentages add up to a number slightly higher or lower than 100. That happens because QMF sometimes rounds off the individual percentages when it calculates them.

Charts:

The above information on how usage codes affect reports is also true for charts. Some of these codes might not be as meaningful in a chart as in a report for the following reasons:

Cumulative percentages or sums can be difficult to express in a meaningful way graphically.
Errors that cause undefined data values are considered null values. These values appear as question marks in a report.
If any of the following symbols are contained in a report to be charted, they are considered null values:
- Hyphens represent null values in a report.
- Asterisks represent data values too long for the width of the column.
- Greater-than signs (>) represent arithmetic overflow.
- Question marks (?) represent undefined values.