SUMMARIZE command

Used to group records based on unique values in one or more character or datetime fields. Counts the number of records in each group, and also subtotals specified numeric fields for each group.

Syntax

SUMMARIZE <ON> key_field <...n> <SUBTOTAL numeric_field <...n>>
<OTHER field <...n>> <TO SCREEN|table_name|PRINT> <IF test> <WHILE test>
<{FIRST|NEXT} range> <PRESORT>  <APPEND> <OPEN> <LOCAL> <HEADER header_text>
<FOOTER footer_text> <STATISTICS> <ISOLOCALE locale_code>

Parameters

ON key_field <...n>

Specifies one or more character or datetime fields to summarize on. Multiple fields must be separated by spaces. The ON parameter can be omitted.

SUBTOTAL numeric_field <...n>

Optional. Specifies one or more numeric fields or expressions to subtotal for each group. Multiple fields must be separated by spaces. Specify ALL to subtotal all the numeric fields in the table.

OTHER field <...n>

Optional. Specifies one or more additional fields to include in the output.

You should only use the OTHER parameter with fields that contain the same value for all records in each summarized group. If you specify a 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.

An example of an appropriate “other field” would be customer name if you were summarizing a table on customer number. In this case, the customer name is identical for all records with the same customer number.

TO SCREEN | table_name | PRINT
  • Specify TO SCREEN to display the results in the ACL display area. This is the default output if the TO parameter is not specified.

  • Specify TO table_name to save the results to an ACL table. You must specify the table_name value as a quoted string with a .FIL file extension to create an ACL table.

    For example: TO "Output.FIL"

    You can also specify an absolute or relative file path to save the .FIL file to an existing folder.

    For example: TO "C:\Output.FIL" or TO "Results\Output.FIL"

  • Specify TO PRINT to send the results to the default printer.

IF test

Optional. Specifies a condition that must be met. The command is executed on only those records that meet the condition.

WHILE test

Optional. Specifies that the command is executed only while a condition is true. The command processing terminates as soon as the specified condition evaluates as false, or the end of the table is reached.

FIRST | NEXT range

Optional. Specify FIRST to start processing at the first record or NEXT to start processing at the currently selected record. Use the range value to specify the number of records to process.

PRESORT

Optional. Sorts the primary table on the primary key before executing the command. You cannot use PRESORT inside the GROUP command.

APPEND

Optional. Specifies that command output is appended to the end of an existing file instead of overwriting it. When using this parameter, you must ensure that the structure of the command output and the existing file is identical, if maintaining a consistent file structure is important (for example, when you are appending data to an existing ACL table), because ACL appends output to an existing file regardless of its structure.

OPEN

Optional. Specifies that the table created by the command is opened after the command executes. This parameter is only valid if the command creates an output table.

LOCAL

Optional. Specifies that the output file is saved in the same location as the ACL project. This parameter only applies when the command is run on an ACL Server table and the output file is an ACL table.

HEADER header_text

Optional. Specifies the text to insert at the top of each page of a report. The header_text value must be specified as a quoted string. The value overrides any value set for the ACL HEADER system variable.

FOOTER footer_text

Optional. Specifies the text to insert at the bottom of each page of a report. The footer_text value must be specified as a quoted string. The value overrides any value set for the ACL FOOTER system variable.

STATISTICS

Optional. Specifies that average, minimum, and maximum values are calculated for all SUBTOTAL fields. If you specify the STATISTICS parameter you must also specify the SUBTOTAL parameter with at least one field.

ISOLOCALE locale_code

Optional. This parameter is only available in the Unicode edition of ACL. Specifies the locale. If you do not specify a locale, ACL uses the default locale. Locale codes are based on the ISO 639 standard language codes and the ISO 3166 standard country codes. Enter locale_code in the format language code_country code. For example, enter the code fr_ca for Canadian French. If you do not specify a country, ACL uses the default country for the language.

Remarks

The SUMMARIZE command groups records that have the same value, or the same combination of values, in one or more character or datetime fields. The output results contain a single record for each group, with a count of the number of records in the source table that belong to the group.

The SUMMARIZE command can process either sorted or unsorted data. If you use PRESORT, or if the table is already sorted, the output results are sorted and contain a single group for each unique value, or unique combination of values, in the summarized field or fields. If you do not use PRESORT, the output results use the order of the source table. If the summarized field or fields contain non-sequential identical values, the output results contain more than one group for each unique value, or unique combination of values, which depending on the context can defeat the purpose of summarizing.

You can summarize a numeric field by using the STRING( ) function to create an expression that converts the numeric data to character data. For example:

SUMMARIZE ON STRING(Unit_Cost, 22) TO "Identical_unit_costs.fil" PRESORT

creates output results that group and count identical unit costs. For more information, see STRING( ) function.

If you use the optional STATISTICS parameter, average, minimum, and maximum values are calculated for each subtotaled numeric field. If you output the results to an ACL table, the fields generated by the SUBTOTAL and STATISTICS parameters use the following naming conventions:

Table 1. Naming conventions for subtotal and statistics fields

Subtotal field

Average field

Minimum field

Maximum field

field name in output table

subtotaled field name in source table

a_subtotaled field name in source table

m_subtotaled field name in source table

x_subtotaled field name in source table

alternate column title in output table

(the column display name in the view)

Total + subtotaled alternate column title in source table

Average + subtotaled alternate column title in source table

Minimum + subtotaled alternate column title in source table

Maximum + subtotaled alternate column title in source table

Examples

a. Total transaction amount per customer

The following example summarizes an accounts receivable table on the Customer Number field, and subtotals the Transaction Amount field. The output results are grouped by customer, and include the total transaction amount for each customer.

OPEN Ar
SUMMARIZE ON Customer_Number SUBTOTAL Trans_Amount TO "Customer_total.FIL"
PRESORT

b. Total transaction amount per customer per transaction date

The following example summarizes an accounts receivable table on the Customer Number and Transaction Date fields, and subtotals the Transaction Amount field. The output results are grouped by customer, and within customer by date, and include the total transaction amount for each customer for each date the customer had transactions.

OPEN Ar
SUMMARIZE ON Customer_Number Trans_Date SUBTOTAL Trans_Amount TO
"Customer_total_by_date.FIL" PRESORT

c. Total, average, minimum, and maximum transaction amounts per customer per transaction date

If you add the STATISTICS parameter, in addition to the subtotaled transaction amount for each customer for each date the customer had transactions, you also calculate the average, minimum, and maximum transaction amounts for each customer for each date.

OPEN Ar
SUMMARIZE ON Customer_Number Trans_Date SUBTOTAL Trans_Amount TO
"Customer_stats_by_date.FIL" PRESORT STATISTICS


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