You use an aggregate function, or set function, such as sum or count, to perform a calculation on data read in from one column, up to the occurrence of a break in another column. For instance, in the section, Population Example in the appendix, “Report-Writer Report Examples,” the regional population subtotals represent use of the sum aggregate on each of the columns tot, tot_18 to 65, tot_under18, and tot_over65 up to a break in region. Additionally, the population totals at the end of the report represent use of the sum aggregate for the same columns up to a break in “report.”

You specify which data must be used in the calculation by naming the column containing that data as a parameter of the aggregate function. In the POPULATION example, the columns containing the relevant data are tot, tot_18 to 65, tot_under18, and tot_over65. You indicate the cut-off point for the data to be included in each calculation by placing the aggregate function within the footer section for a particular column or section of the report. The aggregate value is calculated each time a break occurs in the specified footer.

Aggregates can be non-unique or unique, simple, or cumulative. A non-unique aggregate performs a calculation based on every value read in from the aggregate column up to a break in the specified footer. A unique aggregate performs a calculation on each break value in the aggregate column, up to a break in the specified footer. (Depending on how the data is sorted and where the aggregate is specified, the break values can or cannot be the actual unique values in a column.)

A simple aggregate produces a single value, calculated on all the values in the aggregate column up to a break in the specified footer. A cumulative aggregate calculates a running total for each value in the aggregate column up to the break containing the aggregate instruction. Simple and cumulative aggregates can be either non-unique or unique. Aggregate types are discussed in more detail later in this section.

The following aggregates are allowed:

The basic syntax of an aggregate specification is:

where:

The scope of a simple non-unique aggregate is determined by the context in which it is specified. For example, if you specify sum (salary) in the footer for the report, it refers to the sum of salary for all rows read in the report. If you specify sum (salary) in the page footer, it refers to the sum of salary for all rows that were processed during the printing of each page. If specified in the footer for a break in department, sum (salary) refers to the sum of salary for all rows in each department.

You can specify simple aggregates only in the .footer section for breaks, because these calculations are intended to provide summary information.

You specify a unique aggregate by following the aggregate name with the letter “u,” as in sumu, countu or avgu, respectively. The difference between a unique and a non-unique aggregate is that a unique aggregate performs an operation only when the value in the aggregate column changes, while a non-unique aggregate performs the operation for every value in the aggregate column. Therefore, a unique aggregate performs its calculation only on the break values in the specified column, up to the break containing the aggregate instruction.

For example, if you specify the aggregate, count(region), in the report footer for the sample report in the Population Example section of the appendix,
“Report-Writer Report Examples,” the result would be 51 (remember District of Columbia), because there are 51 rows in the report. However, if you specify “countu(region)” instead, the result would be 9, because nine breaks occur on region.

The number of breaks is not necessarily the same as the actual unique values in the column. This result depends on the break in which the aggregate instruction is placed, and on whether the data in the aggregate column has been sorted or not. For instance, countu would produce a result of 3 on the following unsorted data in Column 1, even though the data contains only two unique values, because three breaks would occur:

Preceding an aggregate name with the keyword cumulative or cum indicates that the cumulative value of an aggregate is to be calculated and printed. As such, you can specify cumulatives in any context (for instance, in detail sections), because you use them to provide running totals. You can apply a cumulative to any of the other aggregates. Cumulatives are particularly useful for applications that need to use running totals, such as account balance applications.

If you do not specify a breakname after the cumulative keyword, or if you specify a breakname of report, Report-Writer assumes that the cumulative aggregate refers to all data rows processed since the start of the report. If a breakname of page is specified, the cumulative aggregate refers to all data rows processed since the last page break. If a breakname is specified which is one of the break columns, the cumulative aggregate refers to all data rows processed since the last break in that column.

You can specify the preset parameter to set the cumulative function to a constant value or to the value of a column when it is initialized (that is, at the start of the break in breakname). For example, in the Account Example section of the appendix, “Report-Writer Report Examples,” the aggregate, cum(acctnum) sum(amt,balance), in the detail block indicates a common use of the preset parameter. When a break occurs in acctnum, Report-Writer sets the cumulative function to the value of balance. As each new transaction is processed, Report-Writer adds the value of amt to the cumulative aggregate. Because deposits are positive and withdrawals are negative, the cumulative aggregate reflects the running balance.

By specifying the +t flag on the report command line, aggregates utilize the rounded values for any floating point column whose format has been specified in a .format statement with a template or as numeric F. (For additional information, see Format Specifications.) That is, the value of the aggregate for such a column is derived from the rounded values for the individual column rows. To force the aggregate to utilize the actual, rather than the rounded, values, specify the ‑t flag on the report Command--Run a Report on page 486.

Here are some examples of aggregates:

Specified in footer for dept, this element gives the minimum value of salary for all data rows in a dept.

Specified in the footer for class, this element gives the average age for all data rows in a class.

Specified in the footer for the report, this element gives the count of the number of data rows in the report + 200.

Specified in the footer for acct, this element gives the sum of transact, initialized by the value of oldbal at the start of each acct.

Specified in the detail text, this element gives the cumulative average of height since the start of the report.

Specified in the detail text, this element gives the cumulative sum of amt since the last change in acctnum and initialized by the value of balance at the last change of value in acctnum.