COUNT Statement (DQL)

The COUNT statement specifies the information to be located in the database. The COUNT statement contains three major parts: the COUNT, the selection criteria and the relationship criteria. Criteria can be omitted.
datacom151
The COUNT statement specifies the information to be located in the database. The COUNT statement contains three major parts: the COUNT, the selection criteria and the relationship criteria. Criteria can be omitted.
The COUNT statement must be the first entry of a COUNT query. When you execute a COUNT statement, 
Dataquery
 locates the specified data and reports on the number of rows found by displaying a message on the screen.
Syntax Diagram
Following is the syntax for the COUNT statement.
►►─ COUNT
table-name
─┬────────────┬─┬────────┬─┬───────────────┬──────────────► └─ (
status
) ─┘ └─ ROWS ─┘ └─
with-clause
─┘ ►─┬───────────────────────┬──────────────────────────────────────────────────►◄ └─
relationship-clause
─┘
The following explains each keyword in a COUNT statement.
  •  
     
    table-name
     
    Represents the name of the primary table containing the rows 
    Dataquery
     is to count. Only those tables shown in your Directory of Tables are available for query access.
  •  
     
    (status)
     
    (Optional)
     If authorized, use to specify a 
    Datacom Datadictionary
     definition of a table that is being tested by programmers responsible for its maintenance. If the FIND or COUNT statement specifies a TEST status for a table, all subsequent statements in the same query reference the same status and version of the table, unless a different status is specified. The following chart describes each status you can specify.
     The status must be enclosed in parentheses, and there must be no spaces between the table name and the left parenthesis.
  •  
    ROWS
    Used for readability only.
  •  
     
    with-clause
     
    Represents one or more logical expressions which specify the criteria 
    Dataquery
     is to use to qualify rows to be counted. A WITH clause contains the criteria which qualify the rows. Only those rows which meet the specified criteria in the WITH clause are counted, as in the following example:
    COUNT CAI-SLSHST-REC ROWS WITH SHIP-QTY GT 0
    The selection criteria can consist of multiple conditions which serve to limit the number of rows to those which meet all conditions. See WITH Clauses (DQL) for more information.
    You can decide not to include any selection criteria. This causes 
    Dataquery
     to count all the rows in the specified table.
  •  
     
    relationship-clause
     
    Represents a relationship expression which relates the data in two or more tables. A relationship clause establishes the relationship between tables. When you use RELATED BY to join tables, you can access all the data contained in each row of the relationship, as in the following example:
    COUNT CAI-SLSHST-REC ROWS WITH SHIP-QTY GT 0 RELATED BY SLMN-ID TO CAI-ORDER-REC ROWS WITH ORD-YR LT '87'
    When you use an outer join keyword, you can retrieve unrelated rows, when needed. See Outer Joins for details. Multiple relationship expressions can exist in a COUNT statement. In addition, you can specify selection criteria for each relationship clause.