APPROX_COUNT

Syntax

Description of the illustration approx_count.eps

Purpose

APPROX_COUNT returns the approximate count of an expression. If you supply MAX_ERROR as the second argument, then the function returns the maximum error between the actual and approximate count.

You must use this function with a corresponding APPROX_RANK function in the HAVING clause. If a query uses APPROX_COUNT, APPROX_SUM, or APPROX_RANK, then the query must not use any other aggregation functions.

For the semantics of the FILTER clause, see FILTER Clause Semantics.

Examples

The following query returns the 10 most common jobs within every department:

SELECT department_id, job_id, 
       APPROX_COUNT(*) 
FROM   employees
GROUP BY department_id, job_id
HAVING 
  APPROX_RANK ( 
  PARTITION BY department_id 
  ORDER BY APPROX_COUNT(*) 
  DESC ) <= 10;