7.22.3 COUNT : Hitachi Advanced Database SQL Reference

(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):

Specify a value expression. For details about value expressions, see 7.20 Value expression.

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

COUNT(DISTINCT value-expression):

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

The examples below illustrate the difference in execution results for the two specification formats above. In the second example, a GROUP BY clause is specified in the SELECT statement.

(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 data type of the execution result is INTEGER.
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