COUNT

Syntax

For information on syntax and semantics, see "Analytic Functions".

Purpose

COUNT returns the number of rows in the query. You can use it as an aggregate or analytic function.

If you specify DISTINCT, you can specify only the query_partition_clause of the analytic_clause. The ORDER_BY_clause and windowing_clause are not allowed.

If you specify expr, COUNT returns the number of rows where expr is not null. You can count either all rows, or only distinct values of expr.

If you specify the asterisk (*), this function returns all rows, including duplicates and nulls. COUNT never returns null.

See Also: "Aggregate Functions".

Aggregate Examples

SELECT COUNT(*) "Total" FROM emp;

Total     
----------
        14

SELECT COUNT(mgr) "Count" FROM emp;

Count     
----------
        13

SELECT COUNT(DISTINCT mgr) "Managers" FROM emp;

Managers   
----------
         6

Analytic Example

The following example calculates, for each employee in the EMP table, the moving count of employees earning salaries in the range $50 less than through $150 greater than the employee's salary.

SELECT ename, sal,
   COUNT(*) OVER (ORDER BY sal RANGE BETWEEN 50 PRECEDING
      AND 150 FOLLOWING) AS mov_count
   FROM emp;

ENAME             SAL  MOV_COUNT
---------- ---------- ----------
SMITH             800          2
JAMES             950          2
ADAMS            1100          3
WARD             1250          3
MARTIN           1250          3
MILLER           1300          3
TURNER           1500          2
ALLEN            1600          1
CLARK            2450          1
BLAKE            2850          4
JONES            2975          3
SCOTT            3000          3
FORD             3000          3
KING             5000          1