Oracle_Count函数

COUNT

Syntax

Description of the illustration count.gif

See Also:

"Analytic Functions" for information on syntax, semantics, and restrictions

Purpose

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

If you specify DISTINCT, then you can specify only the query_partition_clause of theanalytic_clause. The order_by_clause and windowing_clause are not allowed.

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

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

See Also:

"About SQL Expressions" for information on valid forms ofexpr and "Aggregate Functions"

Aggregate Examples

The following examples use COUNT as an aggregate function:

SELECT COUNT(*) "Total"  FROM employees;     Total----------       107SELECT COUNT(*) "Allstars"  FROM employees  WHERE commission_pct > 0; Allstars---------       35SELECT COUNT(commission_pct) "Count"  FROM employees;     Count----------        35SELECT COUNT(DISTINCT manager_id) "Managers"  FROM employees;  Managers----------        18

Analytic Example

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

SELECT last_name, salary,       COUNT(*) OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING AND                      150 FOLLOWING) AS mov_count  FROM employees  ORDER BY salary, last_name;LAST_NAME                     SALARY  MOV_COUNT------------------------- ---------- ----------Olson                           2100          3Markle                          2200          2Philtanker                      2200          2Gee                             2400          8Landry                          2400          8Colmenares                      2500         10Marlow                          2500         10Patel                           2500         10

SQL> select comm from emp;      COMM----------       300       500      1400         0      COMM----------14 rows selected.SQL> select distinct comm from emp;      COMM----------      1400       500       300         05 rows selected.

SQL> select comm from emp;      COMM----------       300       500      1400         0      COMM----------14 rows selected.SQL> select distinct comm from emp;      COMM----------      1400       500       300         05 rows selected.

SQL> select count(comm) from emp where comm is not null;COUNT(COMM)-----------          41 row selected.SQL> select count(comm) from emp where comm is null;COUNT(COMM)-----------          01 row selected.

总结：count(*)结果包含NULL行，count(某一列)不包含空行。