SQL Reference Guide

COUNT

Syntax

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

Description

The aggregate function COUNT computes either the number of rows in a group of rows or the number of non-null values in a group of values.

• The keyword DISTINCT specifies that the duplicate values are to be eliminated before computing the count.
• If the argument to COUNT function is ‘*’, then the function computes the count of the number of rows in group.

  For fixed length files, an exact row count is stored in the file header and can be immediately returned. For variable length files, an index is required that returns the number of keys. The first index that is found from the following is chosen: RECBYT, ROWID, first unique Index, first duplicate index. If no index is available, then a physical table scan is performed to count the actual number of rows in the table.

• If the argument to COUNT function is not ‘*’, then null values are eliminated before the number of rows is computed.
• The argument column_ref or expression can be of any type.
• The result of the function is of INTEGER type. The result is never null.

Example

SELECT COUNT (*)

FROM orders

WHERE order_date = SYSDATE ;