COUNT Function

Description

The COUNT function returns the number of of rows returned by a query. If an asterisk (*) is specified, the number of all rows satisfying the condition (including the rows with the NULL value) is returned. If the DISTINCT or UNIQUE keyword is specified in front of the expression, only the number of rows that have a unique value (excluding the rows with the NULL value) is returned after duplicates have been removed. Therefore, the value returned is always an integer and NULL is never returned.

A column that has collection type and object domain (user-defined class or multimedia class) can also be specified in the expression.

Syntax

COUNT ( * | [ { DISTINCT | DISTINCTROW } | UNIQUE | ALL ] expression )

• expression : Specifies an expression.
• ALL : Gets the number of rows given in the expression (default).
• DISTINCT or UNIQUE : Gets the number of rows without duplicates.

Example

The following is an example that returns the number of Olympic Games that had a mascot. (demodb)

SELECT COUNT(*)

FROM olympic

WHERE mascot IS NOT NULL;

Result value : 9