Oracle® Database SQL Reference 10g Release 1 (10.1) Part Number B10759-01 |
|
|
View PDF |
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 the analytic_clause
. The order_by_clause
and windowing_clause
are not allowed.
If you specify expr
, then 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 (*), 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 " |
The following examples use COUNT
as an aggregate function:
SELECT COUNT(*) "Total" FROM employees; Total ---------- 107 SELECT COUNT(*) "Allstars" FROM employees WHERE commission_pct > 0; Allstars --------- 35 SELECT COUNT(commission_pct) "Count" FROM employees; Count ---------- 35 SELECT COUNT(DISTINCT manager_id) "Managers" FROM employees; Managers ---------- 18
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; LAST_NAME SALARY MOV_COUNT ------------------------- ---------- ---------- Olson 2100 3 Markle 2200 2 Philtanker 2200 2 Landry 2400 8 Gee 2400 8 Colmenares 2500 10 Patel 2500 10 . . .