SQL Reference Guide

Previous Topic

Next Topic



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


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.



FROM orders

WHERE order_date = SYSDATE ;