Counting Values

In this section:

The COUNT command counts the number of instances that exist for a specified field. The COUNT command is particularly useful combined with the BY phrase, which is discussed in Sorting Tabular Reports.

COUNT counts the instances of data contained in a report, not the data values.

For COUNT syntax, see Use Display Commands in a Request.

By default, a COUNT field is a five-digit integer. You can reformat it using the COMPUTE command, and change its field length using the SET COUNTWIDTH parameter. For details about the COMPUTE command, see Creating Temporary Fields. For information about SET COUNTWIDTH, see the TIBCO WebFOCUS® Developing Reporting Applications manual.

When COUNT is used in a request, the word COUNT is appended to the default column title, unless the column title is changed with an AS phrase.

Example: Counting Values

To determine how many employees are in the EMPLOYEE data source, you can count the instances of EMP_ID, the employee identification number. 

TABLE FILE EMPLOYEE
COUNT EMP_ID
END

The following shows the output of the request. 

EMP_ID
 
COUNT
 
------
 
    12

Example: Counting Values With a Sort Phrase

To count the instances of EMP_ID for each department, use this request: 

TABLE FILE EMPLOYEE
COUNT EMP_ID
BY DEPARTMENT
END

The following shows the output of the request indicating that of the 12 EMP_IDs in the data source, six are from the MIS department and six are from the PRODUCTION department: 

            EMP_ID
DEPARTMENT  COUNT 
----------  ------
MIS              6
PRODUCTION       6

Example: Counting Instances of Data

The following example counts the instances of data in the LAST_NAME, DEPARTMENT, and JOBCODE fields in the EMPLOYEE data source. 

TABLE FILE EMPLOYEE
COUNT LAST_NAME AND DEPARTMENT AND JOBCODE
END

The following shows the output of the request. 

LAST_NAME  DEPARTMENT  JOBCODE
COUNT      COUNT       COUNT  
---------  ----------  -------
       12          12       19

The EMPLOYEE data source contains data on 12 employees, with one instance for each LAST_NAME. While there are only two values for DEPARTMENT, there are 12 instances of the DEPARTMENT field because each employee works for one of the two departments. Similarly, there are 19 instances of the JOBCODE field because employees can have more than one job code during their employment.

Counting Segment Instances

You can easily count the instances of the lowest segment in the left path of a data source by specifying an asterisk (*) wildcard instead of a specific field name. In a single-segment data source, this effectively counts all instances in the data source.

COUNT * accomplishes this by counting the values of the first field in the segment. Instances with a missing value in the first field are not counted (when SET MISSING=ON).

Segment instances in short paths are not counted by COUNT *, regardless of the value of the ALL parameter of the SET command.

For more information about missing values, short paths, and the SET ALL parameter, see Handling Records With Missing Field Values.

Example: Counting Segments From a Multi-Path Data Source

The following request counts the number of instances of the SKILLSEG segment of the EMPLOYEE data source. 

TABLE FILE EMPLOYEE
COUNT *
END

The following shows the output of the request. 

COUNT *
 
COUNT
 
-------
 
     19

COUNT * counts the number of instances of the SKILLSEG segment, which is the lowest segment in the left path of the EMPLOYEE data source structure (that is, the EMPLOYEE data source joined to the JOBFILE and EDUCFILE data sources). You can see a picture of the path structure in Displaying the Structure and Retrieval Order of a Multi-Path Data Source.

Tip: In some environments, the following warning is displayed if you use COUNT * with a multi-path data source (such as EMPLOYEE in the above example): 

(FOC757) WARNING. YOU REQUESTED PRINT * OR COUNT * FOR A MULTI-PATH FILE