7.23.3 COUNT

COUNT determines the row count (number of results).

Organization of this subsection

(1) Specification format
(2) Explanation of specification format
(3) Rules
(4) Example

(1) Specification format

general-set-function-COUNT ::= {COUNT([ALL] value-expression)|COUNT(DISTINCT value-expression)}

To Page Top

(2) Explanation of specification format

COUNT([ALL] value-expression):

Specifies a value expression. For details about value expressions, see 7.21 Value expression.

ALL can be omitted. The results will be the same regardless of whether it is specified.

COUNT(DISTINCT value-expression):

Specifies a value expression. For details about value expressions, see 7.21 Value expression. For rows containing the same value, the count will exclude duplicates.

Note that array data and structure data is not permitted for the value expression if DISTINCT is specified.

The examples below illustrate the difference in execution results for the two specification formats above. The cases where the GROUP BY clause is not specified and GROUP BY clause is specified are explained.

(a) Example 1: Without GROUP BY clause

[Figure]

Using the employee table (EMPLIST) above, this example executes the following SELECT statement:

SELECT COUNT("NAME") AS "COUNT-ALL",
       COUNT(DISTINCT "NAME") AS "COUNT-DISTINCT"
    FROM "EMPLIST"

The results are as follows:

[Figure]

Explanation

In the case of COUNT(NAME), duplicates of the same name (Taro Tanaka) are counted, but rows with null values are not counted, so the execution result is 5.
In the case of COUNT(DISTINCT NAME), duplicates of the same name (Taro Tanaka) are not counted, and neither are rows with null values, so the execution result is 3.

(b) Example 2: With GROUP BY clause

Using the employee table (EMPLIST) shown in Example 1, this example executes the following SELECT statement:

SELECT "SCODE",COUNT("NAME") AS "COUNT-ALL",
       COUNT(DISTINCT "NAME") AS "COUNT-DISTINCT"
    FROM "EMPLIST"
    GROUP BY "SCODE"

The results are as follows:

[Figure]

Explanation

In the case of COUNT(NAME), because duplicates of the same name (Taro Tanaka) are counted, the execution result is 3. In the case of COUNT(DISTINCT NAME), duplicates of the same name (Taro Tanaka) are not counted, so the execution result is 2.
Because there are no duplicate rows, the execution result is 1 is both cases.
Because there are no duplicate rows, and rows with null values are not counted, the execution result is 1 is both cases.

To Page Top

(3) Rules

You cannot specify binary data for the value expression.
Null values are not included in the calculation.

The following table shows the data type that can be specified in the value expression and the data type of the execution result of the general set function COUNT.

Table 7‒23: Relationship between data type that can be specified in the value expression and data type of the execution result of the general set function COUNT
No.	Data type that can be specified in the value expression	Data type of the execution result of general set function COUNT
1	INTEGER	BIGINT^#
2	BIGINT
3	SMALLINT
4	DECIMAL(`m`,`n`)
5	NUMERIC(`m`,`n`)
6	DOUBLE PRECISION
7	FLOAT
8	REAL
9	CHARACTER(`n`)
10	VARCHAR(`n`)
11	STRING
12	DATE
13	TIME(`p`)
14	TIMESTAMP(`p`)
15	BOOLEAN
16	ARRAY
17	STRUCT

#: If the data format of the integer data type is a legacy format, the data type of the execution result will be an INTEGER type.

In the following cases, the execution result will be 0.
- If the number of input rows is 0
- If the values to be calculated are all null values
If an overflow occurs during the computation of any set function, an overflow error is generated.

To Page Top

(4) Example

Example

Using the data in the sales history table (SALESLIST), this example determines the number of people who purchased products on or after January 1, 2014, counting those who made more than one purchase as a single person.

SELECT COUNT(DISTINCT "USERID") AS "COUNT"
    FROM "SALESLIST"
        WHERE "PUR-DATE">=DATE'2014-01-01'

Example of execution results

[Figure]

To Page Top