Specifying Result Tables › Counting

Counting

The COUNT(*) function lets you tally the number of rows in the result table. The COUNT(DISTINCT column-name) form of this function returns the number of distinct values in the specified column.

The following example uses the COUNT(DISTINCT column-name) function in a SELECT INTO statement.

 Problem
Find the number of unique customer states contained in CUSTOMERS.
 Solution

               .
               .
       (COBOL statements)
               .
               .
  1   EXEC SQL
  2       SELECT COUNT(DISTINCT STATE)
  3       INTO :WC-COUNT
  4       FROM CUSTOMERS
  5   END-EXEC.
               .
               .
       (COBOL statements)
               .
               .

Line 2

The COUNT(DISTINCT STATE) function tallies the number of distinct values in the STATE column of CUSTOMERS. The result of the SELECT statement is the result of the COUNT function, which is a number, not a result table.

Line 3

The INTO clause specifies that the value returned by the COUNT function is placed in the host variable :WC-COUNT. Hyphens can be used in a COBOL item which is referenced in an SQL statement.