COUNT

The COUNT function returns the number of rows returned by the query. You can use it as an aggregate or analytic function. See Aggregate Functions for more information on aggregate functions. See Analytic Functions for information on analytic functions.

SQL Syntax

COUNT ({* | [ALL | DISTINCT]{Expression|ROWID}}) 
[OVER ([AnalyticClause])]

Parameters

COUNT has the parameters:

Parameter Description

Expression

Can be any numeric data type or any non-numeric type that can be implicitly converted to a numeric type.

Returns the number of rows, where Expression is not null. For more information on the number of rows in a table, see the description for the NUMTUPS field in SYS.TABLES in Oracle TimesTen In-Memory Database System Tables and Views Reference.

*

If you specify *, the COUNT function returns all rows, including duplicate and null values. COUNT does not ever return null.

ALL

Includes duplicate rows in the argument of an aggregate function. If neither ALL nor DISTINCT is specified, ALL is assumed.

DISTINCT

Eliminates duplicate column values from the argument of an aggregate function.

ROWID

TimesTen assigns a unique ID called a rowid to each row stored in a table. The rowid value can be retrieved through the ROWID pseudocolumn. See ROWID Pseudocolumn for more details.

OVER ([AnalyticClause])

If specified, indicates aggregate analytic function. See Analytic Functions for more information on analytic functions.

Description

  • The default return data type is TT_BIGINT. You can specify the TT_CountAsInt optimizer hint to control whether the COUNT function returns a data type of TT_INTEGER or a data type of TT_BIGINT. If you specify a value of 1 for the hint, the return data type is TT_INTEGER. If you specify a value of 0 (or if you do not specify this hint), the return data type is TT_BIGINT. The TT_CountAsInt optimizer hint is supported at the statement and at the connection levels. See Statement Level Optimizer Hints for more information on statement level optimizer hints. See OptimizerHint in the Oracle TimesTen In-Memory Database Reference for information on connection level optimizer hints.

  • If an aggregate function is computed over an empty table in which GROUP BY is not used, COUNT returns 0.

  • If an aggregate function is computed over an empty group or an empty grouped table (GROUP BY is used), COUNT returns nothing.

  • If you do not use the AnalyticClause in your query, then COUNT acts as an aggregate function.

  • If you specify DISTINCT and the AnalyticClause, then you can only specify the QueryPartitionClause. The OrderByClause and WindowingClause are not allowed.

Examples

Count the number of employees.

Command> SELECT COUNT(*) "TOTAL EMP" FROM employees;
 
TOTAL EMP
< 107 >
1 row found.

Count the number of managers by selecting out each individual manager ID without duplication.

Command> SELECT COUNT(DISTINCT manager_id) "Managers" FROM employees;
 
MANAGERS
< 18 >
1 row found.