About summarizing

Summarizing groups the records in a table based on unique values, or unique combinations of values, in one or more character or datetime fields, and counts the number of records in each group. You can also subtotal one or more numeric fields for each group. For example:

Summarizing does not require that tables are sorted. However, if you summarize using a field that contains non-sequential identical values, the results contain more than one summarized group for the same value, which depending on the context can defeat the purpose of summarizing. Summarizing includes the Presort option, which allows you to sort a table as part of summarizing.

The Statistics option

The Include Statistics for Subtotal Fields option allows you to calculate average, minimum, and maximum values for each subtotaled numeric field. In the second example above, using the statistics option would calculate the average, minimum, and maximum transaction amounts for each customer for each date the customer had transactions.

The Other Fields option

The Other Fields option allows you to select additional character, datetime, or numeric fields to include in the output. This option can provide useful information if the fields you select contain values that are the same for all records in each summarized group. For example, if you summarize on customer number and specify a customer name field as an “other field”, the customer name appears with the customer number in the output.

If you specify an “other field” that contains values that are different for a summarized group, only the value for the first record in the group is displayed, which is not meaningful. For example, if you summarized a vendor table by state, and selected city as an “other field”, only the first city listed for each state would appear in the output. In this instance, the better approach is to summarize using both state and city as key fields, in that order.

Summarizing in detail

Summarizing performs the following operations:

Table 1. Operations performed by summarizing

Operation

Location in Figure 1

Groups the records based on unique values, or unique combinations of values, in one or more character or datetime fields

Cust Number field

Trans Type field

Subtotals the number of records for each group

Count field

Optionally subtotals the values of one or more numeric fields for each group

Trans Amount field

Optionally calculates average, minimum, and maximum values for each subtotaled numeric field

not shown

Optionally displays additional character, datetime, or numeric fields with complementary information

Name field

Provides totals for all numeric fields included in the output results

Totals row

Figure 1 provides an example of the results produced by summarizing on customer number and transaction type, and subtotaling on transaction amount, in an accounts receivable table (the ‘Ar’ table in ACL DATA\Sample Data Files\Sample Project.ACL) and outputting the results to screen.

Figure 1. Summarize results

Related concepts
Classifying versus summarizing
About classifying
Related tasks
Summarizing data


(C) 2015 ACL Services Ltd. All Rights Reserved.