APPROX_COUNT_DISTINCT

Syntax

Purpose

APPROX_COUNT_DISTINCT returns the approximate number of rows that contain distinct values ofexpr.

This function provides an alternative to theCOUNT(DISTINCTexpr) function, which returns the exact number of rows that contain distinct values ofexpr.APPROX_COUNT_DISTINCT processes large amounts of data significantly faster thanCOUNT, with negligible deviation from the exact result.

Forexpr, you can specify a column of any scalar data type other thanBFILE,BLOB,CLOB,LONG,LONGRAW, orNCLOB.

APPROX_COUNT_DISTINCT ignores rows that contain a null value forexpr. This function returns aNUMBER.

Examples

The following statement returns the approximate number of rows with distinct values formanager_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. . .