Including Subtotals

You can use the SUBTOTAL and SUB-TOTAL commands to sum individual values, such as columns of numbers, each time a named sort field changes value.

Both SUB-TOTAL and SUBTOTAL produce grand totals. You can suppress grand totals using the NOTOTAL command. See Suppressing Grand Totals.

The subtotal is calculated every time the sort field value changes or, if WHEN criteria are applied to the sort field, every time the WHEN conditions are met.

Syntax: How to Create Subtotals

{BY|ON} fieldname {SUB-TOTAL|SUBTOTAL} [MULTILINES]
      [field1 [AND] field2...] [AS 'text'][WHEN expression;]

where:

fieldname

Must be the name of a field in a sort phrase. A BY phrase can include a summary command. The number of fields to subtotal multiplied by the number of levels of subtotals counts in the number of display fields permitted for the request. For details on determining the maximum number of display fields that can be used in a request, see Displaying Report Data.

SUB-TOTAL|SUBTOTAL

SUB-TOTAL displays subtotals for numeric values when the BY|ON field changes value, and for any higher-level sort fields when their values change.

SUBTOTAL displays a subtotal only when the specified sort field changes value.

MULTILINES

Suppresses the printing of a subtotal line for every sort break that has only one detail line, since the subtotal value is equal to this one value. Note that MULTI-LINES is a synonym for MULTILINES. MULTILINES is not supported with horizontal (ACROSS) sort fields.

field1, field2, ...

Denotes a list of specific fields to subtotal. This list overrides the default, which includes all numeric display fields.

AS 'text'

Enables you to specify a different label. For related information, see .

WHEN expression

Specifies the conditional display of subtotals as determined by a Boolean expression. You must end the expression with a semicolon.

Reference: Usage Notes for Subtotals

When using a SUM or COUNT command with only one sort phrase in the request, SUB-TOTAL and SUBTOTAL produce the same result as the value of the SUM or COUNT command. However, when using a PRINT command with one sort phrase, SUBTOTAL is useful because there can be many values within a sort break.
All SUB-TOTALs display up to and including the point where the sort break occurs, so only the innermost point of subtotaling should be requested. For instance, if the BY fields are
```
BY AREA
BY PROD_CODE
BY DATE SUB-TOTAL
```
then, when AREA changes, subtotals are displayed for DATE, PROD_CODE, and AREA on three lines (one under the other).
If you use a WHERE TOTAL or IF TOTAL test, the display of the sort field value for the subtotal line is suppressed unless PRINTPLUS is ON. For details about using PRINTPLUS in FOCUS, see Using PRINTPLUS in Chapter 3, Viewing and Printing Report Output.
Subtotals display on the next line if the subtotal text does not fit on the line prior to the displayed field columns.
If a report request has multiple BY phrases, with SUBTOTAL/SUMMARIZE/RECOMPUTE/SUB-TOTAL at several levels, and MULTILINES or MULTI-LINES is specified at any one of those levels, it applies to all levels.
Note: ON BYfield SUBFOOT applies only to the level specified.

Top of page

Example: Generating Subtotals

The following request illustrates how to create a subtotal for SALES every time the country value changes.

TABLE FILE CAR
SUM AVE.MPG AND SALES AND AVE.RETAIL_COST
BY COUNTRY SUB-TOTAL SALES
BY BODYTYPE
END

The output is:

                             AVE           AVE        
COUNTRY     BODYTYPE         MPG    SALES  RETAIL_COST
-------     --------         ----   -----  -----------
ENGLAND     CONVERTIBLE        16       0        8,878
            HARDTOP            25       0        5,100
            SEDAN              10   12000       15,671
                                                      
*TOTAL ENGLAND                      12000             
                                                      
FRANCE      SEDAN              21       0        5,610
                                                      
*TOTAL FRANCE                           0             
                                                      
ITALY       COUPE              11   12400       19,160
            ROADSTER           21   13000        6,820
            SEDAN              21    4800        5,925
*TOTAL ITALY                        30200             
                                                      
JAPAN       SEDAN              14   78030        3,239
                                                      
*TOTAL JAPAN                        78030             
                                                      
W GERMANY   SEDAN              20   88190        9,247
                                                      
*TOTAL W GERMANY                    88190             
                                                      
                                                      
TOTAL                              208420

Top of page

Example: Comparing SUB-TOTAL and SUBTOTAL

The following request illustrates how to create a subtotal for the numeric fields DED_AMT and GROSS when the department value changes, and for the higher-level sort field (DED_CODE) when its value changes.

TABLE FILE EMPLOYEE
SUM DED_AMT GROSS BY DED_CODE BY DEPARTMENT
BY BANK_ACCT
WHERE BANK_ACCT NE 0
ON DEPARTMENT SUB-TOTAL
END

If you use SUBTOTAL instead of SUB-TOTAL, the totals for DED_AMT and GROSS display only when the DEPARTMENT value changes.

The first portion of the output is:

DED_CODE  DEPARTMENT  BANK_ACCT          DED_AMT            GROSS
--------  ----------  ---------          -------            -----
CITY      MIS          40950036           $14.00        $6,099.50
                      122850108           $31.75        $9,075.00
                      163800144           $82.70       $22,013.75
                                                                 
*TOTAL DEPARTMENT MIS                    $128.45       $37,188.25
                                                                 
          PRODUCTION     160633            $7.42        $2,475.00
                      136500120           $18.25        $9,130.00
                      819000702           $60.20       $17,094.00
                                                                 
*TOTAL DEPARTMENT PRODUCTION              $85.87       $28,699.00
*TOTAL DED_CODE CITY                     $214.32       $65,887.25

The last portion of the output is:

DED_CODE  DEPARTMENT  BANK_ACCT          DED_AMT            GROSS
--------  ----------  ---------          -------            -----
STAT      MIS          40950036          $196.13        $6,099.50
                      122850108          $444.65        $9,075.00
                      163800144        $1,157.60       $22,013.75
                                                                 
*TOTAL DEPARTMENT MIS                  $1,798.38       $37,188.25
                                                                 
          PRODUCTION     160633          $103.95        $2,475.00
                      136500120          $255.65        $9,130.00
                      819000702          $843.32       $17,094.00
                                                                 
*TOTAL DEPARTMENT PRODUCTION           $1,202.92       $28,699.00
*TOTAL DED_CODE STAT                   $3,001.30       $65,887.25
                                                                 
                                                                 
TOTAL                                 $41,521.18      $461,210.75