Averaging Values of a Field

This request calculates the average number of education hours spent in each department.

TABLE FILE EMPLOYEE
SUM AVE.ED_HRS BY DEPARTMENT
END

The following shows the output of the request.

            AVE   
DEPARTMENT  ED_HRS
----------  ------
MIS          38.50
PRODUCTION   20.00

Averaging the Sum of Squared Fields

This request calculates the sum and the sum of squared fields for the DELIVER_AMT field.

TABLE FILE SALES
SUM DELIVER_AMT AND ASQ.DELIVER_AMT
BY CITY
END

The following shows the output of the request.

                              ASQ        
CITY             DELIVER_AMT  DELIVER_AMT
----             -----------  -----------
NEW YORK                 300          980
NEWARK                    60          900
STAMFORD                 430         3637
UNIONDALE                 80         1600

Calculating Maximum and Minimum Field Values

This report request calculates the maximum and minimum values of SALARY.

TABLE FILE EMPLOYEE
SUM MAX.SALARY AND MIN.SALARY
END

The following shows the output of the request.

    MAX              MIN   
    SALARY           SALARY
    ------           ------
$29,700.00        $8,650.00

Calculating the Median and Mode

The following request against the EMPLOYEE data source displays the current salaries and calculates the average (mean), median, and mode within each department.

TABLE FILE EMPLOYEE
SUM CURR_SAL AS 'INDIVIDUAL,SALARIES'
AVE.CURR_SAL AS 'DEPARTMENT,AVERAGE'
MDN.CURR_SAL AS 'DEPARTMENT,MEDIAN'
MDE.CURR_SAL AS 'DEPARTMENT,MODE'
BY DEPARTMENT
ON TABLE SET PAGE NOPAGE
END

Both departments have an even number of employees. For the MIS department, the two middle values are the same, making that value ($18,480.00) both the median and the mode. For the PRODUCTION department, the median is the average of the two middle values ($16,100.00 and $21,120.00) and, since there are no duplicate values, the mode is the lowest value ($9,500.00).

Calculating Column Percentages

To calculate each employee share of education hours, issue the following request:

TABLE FILE EMPLOYEE
SUM ED_HRS PCT.ED_HRS BY LAST_NAME
ON TABLE COLUMN-TOTAL
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image:

If you set PCFORMAT to OLD, PCT. and RPCT. WILL take the same format as the field, and the column may not always total exactly 100 because of the nature of floating-point arithmetic.

Calculating Row Percentages

The following request calculates the total units sold for each product (UNIT_SOLD column), and the percentage that total makes up in relation to the sum of all products sold (RPCT.UNIT_SOLD column) in each city.

TABLE FILE SALES
SUM UNIT_SOLD RPCT.UNIT_SOLD ROW-TOTAL
BY PROD_CODE
ACROSS CITY    WHERE
CITY EQ 'NEW YORK' OR 'STAMFORD'
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image.

Because UNIT_SOLD has an integer format, the columns created by RPCT. also have integer (I) formats. Therefore, individual percentages may be truncated and the total percentage may be less than 100%. If you require precise totals, redefine the field with a format that declares decimal places (D, F).

Producing a Direct Percent of a Count

This request illustrates the relative percentage of the values in the EMP_ID field for each department.

TABLE FILE EMPLOYEE
SUM PCT.CNT.EMP_ID
BY DEPARTMENT
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image:

Using the Distinct Operator

The procedure requesting a count of unique ED_HRS values is either:

TABLE FILE EMPLOYEE
SUM CNT.DST.ED_HRS
END

or

TABLE FILE EMPLOYEE
COUNT DST.ED_HRS
END

The output is:

COUNT   
DISTINCT
ED_HRS  
--------
       9

Notice that the count includes records for both employees with the last name SMITH, but excludes the second records for values 50.00, 25.00, and .0, resulting in nine unique ED_HRS values.

Counting Distinct Field Values With Multiple Display Commands

The following request against the GGSALES data source counts the total number of records by region, then the number of records, distinct categories, and distinct products by region and by state. The DST or CNT.DST operator can be used only with the last display command:

TABLE FILE GGSALES                     
COUNT CATEGORY AS 'TOTAL,COUNT'        
  BY REGION                            
SUM CNT.CATEGORY AS 'STATE,COUNT'      
    CNT.DST.CATEGORY    CNT.DST.PRODUCT
  BY REGION                            
  BY ST                                
END

The output is:

                                  COUNT     COUNT   
             TOTAL         STATE  DISTINCT  DISTINCT
Region       COUNT  State  COUNT  CATEGORY  PRODUCT 
------       -----  -----  -----  --------  --------
Midwest       1085  IL       362         3         9
                    MO       361         3         9
                    TX       362         3         9
Northeast     1084  CT       361         3        10
                    MA       360         3        10
                    NY       363         3        10
Southeast     1082  FL       361         3        10
                    GA       361         3        10
                    TN       360         3        10
West          1080  CA       721         3        10
                    WA       359         3        10

Retrieving the First Record

The following request retrieves the first logical record in the EMP_ID field:

TABLE FILE EMPLOYEE
SUM FST.EMP_ID
END

The output is:

FST      
EMP_ID   
------   
071382660

Segment Types and Retrieving Records

The EMPLOYEE data source contains the DEDUCT segment, which orders the fields DED_CODE and DED_AMT from lowest value to highest value (segment type of S1). The DED_CODE field indicates the type of deduction, such as CITY, STATE, FED, and FICA. The following request retrieves the first logical record for DED_CODE for each employee:

TABLE FILE EMPLOYEE
SUM FST.DED_CODE
BY EMP_ID
END

The output is:

           FST     
EMP_ID     DED_CODE
------     --------
071382660  CITY    
112847612  CITY    
117593129  CITY    
119265415  CITY    
119329144  CITY    
123764317  CITY    
126724188  CITY    
219984371  CITY    
326179357  CITY    
451123478  CITY    
543729165  CITY    
818692173  CITY

Note, however, the command SUM LST.DED_CODE would have retrieved the last logical record for DED_CODE for each employee.

If the record is in a segment with values organized from highest to lowest (segment type SH1), the first logical record that the FST. prefix operator retrieves is the highest value in the set of values. The LST. prefix operator would therefore retrieve the lowest value in the set of values.

For example, the EMPLOYEE data source contains the PAYINFO segment, which orders the fields JOBCODE, SALARY, PCT_INC, and DAT_INC from highest value to lowest value (segment type SH1). The following request retrieves the first logical record for SALARY for each employee:

TABLEF FILE EMPLOYEE
SUM FST.SALARY
BY EMP_ID
END

The output is:

                    FST   
EMP_ID              SALARY
------              ------
071382660       $11,000.00
112847612       $13,200.00
117593129       $18,480.00
119265415        $9,500.00
119329144       $29,700.00
123764317       $26,862.00
126724188       $21,120.00
219984371       $18,480.00
326179357       $21,780.00
451123478       $16,100.00
543729165        $9,000.00
818692173       $27,062.00

However, the command SUM LST.SALARY would have retrieved the last logical record for SALARY for each employee.

Counting Values With CNT

The following request counts the occurrences of PRODUCT_ID, and sums the value of UNIT_PRICE.

TABLE FILE GGPRODS
SUM CNT.PRODUCT_ID AND UNIT_PRICE
END

The output is:

Product          
Code        Unit 
COUNT       Price
-------     -----
     10    660.00

Summing Values With SUM

The following request counts the occurrences of PRODUCT_ID, and sums the value of UNIT_PRICE.

TABLE FILE GGPRODS
COUNT PRODUCT_ID AND SUM.UNIT_PRICE
END

The output is:

Product          
Code        Unit 
COUNT       Price
-------     -----
     10    660.00

Summing Values With TOT

The following request uses the TOT prefix operator to show the total of current salaries for all employees.

TABLE FILE EMPLOYEE
PRINT LAST_NAME
BY DEPARTMENT
ON TABLE SUBFOOT
"Total salaries equal: <TOT.CURR_SAL"
END

The output is:

DEPARTMENT  LAST_NAME                
----------  ---------                
MIS         SMITH                    
            JONES                    
            MCCOY                    
            BLACKWOOD                
            GREENSPAN                
            CROSS                    
PRODUCTION  STEVENS                  
            SMITH                    
            BANNING                  
            IRVING                   
            ROMANS                   
            MCKNIGHT                 
Total salaries equal:     $222,284.00

Ranking Within Sort Groups

The following request ranks years of service within department and ranks salary within years of service and department. Note that years of service depends on the value of TODAY. The output for this example was valid when run in September, 2006:

DEFINE FILE EMPDATA
  TODAY/YYMD = &YYMD;
  YRS_SERVICE/I9 = DATEDIF(HIREDATE,TODAY,'Y');
END
TABLE FILE EMPDATA
PRINT SALARY
  RNK.YRS_SERVICE AS 'RANKING,BY,SERVICE'
  RNK.SALARY AS 'SALARY,RANK'
     BY DEPT
     BY HIGHEST YRS_SERVICE
     BY HIGHEST SALARY NOPRINT
WHERE DEPT EQ 'MARKETING' OR 'SALES'
ON TABLE SET PAGE NOPAGE
END

The output is:

                                                    RANKING
                                                    BY       SALARY
DEPT                  YRS_SERVICE           SALARY  SERVICE  RANK
----                  -----------           ------  -------  ------
MARKETING                      17       $55,500.00        1       1
                                        $55,500.00        1       1
                               16       $62,500.00        2       1
                                        $62,500.00        2       1
                                        $62,500.00        2       1
                                        $58,800.00        2       2
                                        $52,000.00        2       3
                                        $35,200.00        2       4
                                        $32,300.00        2       5
                               15       $50,500.00        3       1
                                        $43,400.00        3       2
SALES                          17      $115,000.00        1       1
                                        $54,100.00        1       2
                               16       $70,000.00        2       1
                                        $43,000.00        2       2
                               15       $43,600.00        3       1
                                        $39,000.00        3       2
                               15       $30,500.00        3       3

Using RNK. in a WHERE TOTAL Test

The following request displays only those rows in the highest two salary ranks within the years of service category. Note that years of service depends on the value of TODAY. The output for this example was valid when run in September, 2006:

DEFINE FILE EMPDATA
  TODAY/YYMD = &YYMD;
  YRS_SERVICE/I9 = DATEDIF(HIREDATE,TODAY,'Y');
END
TABLE FILE EMPDATA
PRINT LASTNAME FIRSTNAME RNK.SALARY
BY HIGHEST YRS_SERVICE BY HIGHEST SALARY
WHERE TOTAL RNK.SALARY LE 2
END

The output is:

                                                           RANK
YRS_SERVICE           SALARY  LASTNAME         FIRSTNAME   SALARY
-----------           ------  --------         ---------   ------
         17      $115,000.00  LASTRA           KAREN            1
                  $80,500.00  NOZAWA           JIM              2
         16       $83,000.00  SANCHEZ          EVELYN           1
                  $70,000.00  CASSANOVA        LOIS             2
         15       $62,500.00  HIRSCHMAN        ROSE             1
                              WANG             JOHN             1
                  $50,500.00  LEWIS            CASSANDRA        2

Using RNK. in a COMPUTE Command

The following request sets a flag to Y for records in which the salary rank within department is less than or equal to 5 and the rank of years of service within salary and department is less than or equal to 6. Otherwise, the flag has the value N. Note that the years of service depends on the value of TODAY. The output for this example was valid when run in September, 2006:

DEFINE FILE EMPDATA
  TODAY/YYMD = &YYMD;
  YRS_SERVICE/I9 = DATEDIF(HIREDATE,TODAY,'Y');
END
TABLE FILE EMPDATA
PRINT RNK.SALARY RNK.YRS_SERVICE
COMPUTE FLAG/A1 = IF RNK.SALARY LE 5  AND RNK.YRS_SERVICE LE 6
    THEN 'Y' ELSE 'N';
BY DEPT BY SALARY BY YRS_SERVICE
WHERE DEPT EQ 'MARKETING' OR 'SALES'
ON TABLE SET PAGE NOPAGE
END

The output is:

                                                 RANK   RANK
DEPT                          SALARY YRS_SERVICE SALARY YRS_SERVICE FLAG
----                          ------ ----------- ------ ----------- ----
MARKETING                 $32,300.00          16      1           1 Y
                          $35,200.00          16      2           1 Y
                          $43,400.00          15      3           1 Y
                          $50,500.00          15      4           1 Y
                          $52,000.00          16      5           1 Y
                          $55,500.00          17      6           1 N
                                                      6           1 N
                          $58,800.00          16      7           1 N
                          $62,500.00          16      8           1 N
                                                      8           1 N
                                                      8           1 N
SALES                     $30,500.00          15      1           1 Y
                          $39,000.00          15      2           1 Y
                          $43,000.00          16      3           1 Y
                          $43,600.00          15      4           1 Y
                          $54,100.00          17      5           1 Y
                          $70,000.00          16      6           1 N
                         $115,000.00          17      7           1 N

Rolling Up an Average Calculation

The following request against the GGSALES data source contains two sort fields, REGION and ST. The summary command for REGION applies the AVE. operator to the sum of the units value for each state.

TABLE FILE GGSALES
   SUM UNITS AS 'Inventory '
     BY REGION
   BY ST
   ON REGION SUBTOTAL      AVE.  AS 'Average'
   WHERE DATE GE 19971001
   WHERE REGION EQ 'West' OR 'Northeast'
   ON TABLE SET PAGE NOPAGE
   END

On the output, the UNITS values for each state are averaged to calculate the subtotal for each region. The UNITS values for each state are also used to calculate the average for the grand total row.

Region       State  Inventory
------       -----  ----------
Northeast    CT          37234
             MA          35720
             NY          36248

Average Northeast
                         36400

West         CA          75553
             WA          40969

Average West
                         58261


TOTAL                    45144

The following version of the request adds a summary command for the grand total line that includes the ROLL. operator:

TABLE FILE GGSALES
   SUM UNITS AS 'Inventory '
     BY REGION
   BY ST
   ON REGION SUBTOTAL  AVE.  AS 'Average'
   WHERE DATE GE 19971001
   WHERE REGION EQ 'West' OR 'Northeast'
   ON TABLE SUBTOTAL ROLL.AVE. AS ROLL.AVE
   ON TABLE SET PAGE NOPAGE                  
   END

On the output, the UNITS values for each state are averaged to calculate the subtotal for each region, and those region subtotal values are used to calculate the average for the grand total row:

Region       State  Inventory
  ------       -----  ----------
  Northeast    CT          37234
               MA          35720
               NY          36248
  
  Average Northeast  
                           36400     
  
  West         CA          75553
               WA          40969
  
  Average West       
                           58261   
  
  
  ROLL.AVE                 47330

Propagating Rollups to Higher Level Sort Breaks

The following request against the GGSALES data source has three BY fields. The SUBTOTAL command for the PRODUCT sort field specifies AVE., and the SUMMARIZE command for the higher level sort field, REGION, specifies ROLL.AVE.

TABLE FILE GGSALES
SUM UNITS
BY REGION
BY PRODUCT
BY HIGHEST DATE
WHERE DATE GE 19971001
  WHERE REGION EQ 'Midwest' OR 'Northeast'
  WHERE PRODUCT LIKE 'C%'
  ON PRODUCT SUBTOTAL AVE.
  ON REGION SUMMARIZE ROLL.AVE. AS ROLL.AVE
ON TABLE SET PAGE NOPAGE
END

On the output, the detail rows for each date are used to calculate the average for each product. Because of the ROLL.AVE. at the region level, the averages for each product are used to calculate the averages for each region, and the region averages are used to calculate the average for the grand total line:

Region       Product                 Date  Unit Sales
  ------       -------                 ----  ----------
  Midwest      Coffee Grinder    1997/12/01        4648
                                 1997/11/01        3144
                                 1997/10/01        1597
  
  *TOTAL PRODUCT Coffee Grinder                    3129
  
               Coffee Pot        1997/12/01        1769
                                 1997/11/01        1462
                                 1997/10/01        2346
  
  *TOTAL PRODUCT Coffee Pot                        1859    
  
               Croissant         1997/12/01        7436
                                 1997/11/01        5528
                                 1997/10/01        6060
  
  *TOTAL PRODUCT Croissant                         6341     
  ROLL.AVE Midwest                                 3776  
  
  Northeast    Capuccino         1997/12/01        1188
                                 1997/11/01        2282
                                 1997/10/01        3675
  
  *TOTAL PRODUCT Capuccino                         2381   
  
               Coffee Grinder    1997/12/01        1536
                                 1997/11/01        1399
                                 1997/10/01        1315
  
  *TOTAL PRODUCT Coffee Grinder                    1416  
  
               Coffee Pot        1997/12/01        1442
                                 1997/11/01        2129
                                 1997/10/01        2082
  
  *TOTAL PRODUCT Coffee Pot                        1884  
  
               Croissant         1997/12/01        4291
                                 1997/11/01        6978
                                 1997/10/01        4741
  
  *TOTAL PRODUCT Croissant                         5336 
  ROLL.AVE Northeast                               2754   
  
  
  TOTAL                                            3265

Calculating the Standard Deviation of a Population

The following request calculates the standard deviation of the population of the DOLLARS field converted to double precision.

DEFINE FILE ibisamp/ggsales
DOLLARS/D12.2 = DOLLARS;
END
TABLE FILE ibisamp/ggsales
SUM DOLLARS STDP.DOLLARS 
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image.

Using Prefix Operators in a Heading

The following request uses prefix operators in the heading.

TABLE WF_RETAIL_LITE
HEADING
"Heading Calculations:"
"Total:      <TOT.COGS_US"
"Count:                   <TOTCNT.COGS_US"
"Average:           <TOTAVE.COGS_US"
"Minimum:            <TOTMIN.COGS_US"
"Maximum:      <TOTMAX.COGS_US"
SUM COGS_US CNT.COGS_US AS Count AVE.COGS_US AS Average 
MIN.COGS_US AS Minimum MAX.COGS_US AS Maximum
BY BUSINESS_REGION AS Region
BY PRODUCT_CATEGORY AS Category
WHERE BUSINESS_REGION NE 'Oceania'
ON TABLE SUBTOTAL COGS_US CNT.COGS_US  AS Total
ON TABLE SET PAGE NOPAGE
ON TABLE SET SHOWBLANKS ON
ON TABLE SET STYLE *
type=report,grid=off, size=11,$
ENDSTYLE
END

The output is shown in the following image.