Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
Functions, 26 of 166
count::=
count
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.
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 . . .
|
![]() Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|