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 |
---|---|
|
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 |
|
If you specify |
|
Includes duplicate rows in the argument of an aggregate function. If neither |
|
Eliminates duplicate column values from the argument of an aggregate function. |
|
TimesTen assigns a unique ID called a rowid to each row stored in a table. The rowid value can be retrieved through the |
|
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 theTT_CountAsInt
optimizer hint to control whether theCOUNT
function returns a data type ofTT_INTEGER
or a data type ofTT_BIGINT
. If you specify a value of1
for the hint, the return data type isTT_INTEGER
. If you specify a value of0
(or if you do not specify this hint), the return data type isTT_BIGINT
. TheTT_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, thenCOUNT
acts as an aggregate function. -
If you specify
DISTINCT
and theAnalyticClause
, then you can only specify theQueryPartitionClause
. TheOrderByClause
andWindowingClause
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.