Syntax
Purpose
APPROX_COUNT_DISTINCT
returns the approximate number of rows that contain a distinct value for expr
.
This function provides an alternative to the COUNT
(DISTINCT
expr
)
function, which returns the exact number of rows that contain distinct values of expr
. APPROX_COUNT_DISTINCT
processes large amounts of data significantly faster than COUNT
, with negligible deviation from the exact result.
For expr
, you can specify a column of any scalar data type other than BFILE
, BLOB
, CLOB
, LONG
, LONG
RAW
, or NCLOB
.
APPROX_COUNT_DISTINCT
ignores rows that contain a null value for expr
. This function returns a NUMBER
.
See Also:
COUNT for more information on the COUNT
(DISTINCT
expr
)
function
Appendix C in Oracle Database Globalization Support Guide for the collation determination rules, which define the collation APPROX_COUNT_DISTINCT
uses to compare character values for expr
Examples
The following statement returns the approximate number of rows with distinct values for manager_id
:
SELECT APPROX_COUNT_DISTINCT(manager_id) AS "Active Managers" FROM employees; Active Managers --------------- 18
The following statement returns the approximate number of distinct customers for each product:
SELECT prod_id, APPROX_COUNT_DISTINCT(cust_id) AS "Number of Customers" FROM sales GROUP BY prod_id ORDER BY prod_id; PROD_ID Number of Customers ---------- ------------------- 13 2516 14 2030 15 2105 16 2367 17 2093 18 2975 19 2630 20 3791 . . .