> ## Documentation Index
> Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt
> Use this file to discover all available pages before exploring further.

> Reference material for APPROX_COUNT_DISTINCT

# APPROX_COUNT_DISTINCT

Counts the approximate number of unique values that are not NULL.
Internally, `APPROX_COUNT_DISTINCT` uses HyperLogLog (HLL) sketches.
Calling `APPROX_COUNT_DISTINCT` returns the same value as calling `HLL_COUNT_DISTINCT` with precision 17.

## Syntax

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
APPROX_COUNT_DISTINCT(<expression>) [FILTER ([WHERE] <condition>)]
```

## Parameters

| Parameter      | Description                                                       | Supported input types |
| :------------- | :---------------------------------------------------------------- | :-------------------- |
| `<expression>` | Expression on which to approximate the distinct count             | Any type              |
| `<condition>`  | An optional boolean expression to filter rows used in aggregation | `BOOL`                |

## Return Type

`BIGINT`

## Example

When aggregating on few distinct values, `APPROX_COUNT_DISTINCT` has no estimation error and returns exact results:

<div className="query-window">
  ```
  SELECT
    APPROX_COUNT_DISTINCT(number) AS approximate,
    COUNT(DISTINCT number) AS exact
  FROM generate_series(1, 1000) r(number);
  ```

  | approximate <span>long</span> | exact <span>long</span> |
  | :---------------------------- | :---------------------- |
  | 1000                          | 1000                    |

  <p><span>Rows: 1</span><span>Execution time: 9.21ms</span></p>
</div>

`NULL` values are ignored by `APPROX_COUNT_DISTINCT`. Including `NULL` in the input does not change the result:

<div className="query-window">
  ```
  SELECT
    APPROX_COUNT_DISTINCT(number) AS approximate,
    COUNT(DISTINCT number) AS exact
  FROM (
    SELECT * FROM generate_series(1, 1000)
    UNION ALL
    SELECT NULL
  ) r(number);
  ```

  | approximate <span>long</span> | exact <span>long</span> |
  | :---------------------------- | :---------------------- |
  | 1000                          | 1000                    |

  <p><span>Rows: 1</span><span>Execution time: 11.74ms</span></p>
</div>

As the number of distinct values grows, the result becomes an approximation:

<div className="query-window">
  ```
  SELECT
    APPROX_COUNT_DISTINCT(number) AS approximate,
    COUNT(DISTINCT number) AS exact
  FROM generate_series(1, 50000) r(number);
  ```

  | approximate <span>long</span> | exact <span>long</span> |
  | :---------------------------- | :---------------------- |
  | 50160                         | 50000                   |

  <p><span>Rows: 1</span><span>Execution time: 16.67ms</span></p>
</div>

`APPROX_COUNT_DISTINCT` also works for compound types such as arrays:

<div className="query-window">
  ```
  SELECT
    APPROX_COUNT_DISTINCT(arr) AS approximate,
    COUNT(DISTINCT arr) AS exact
  FROM UNNEST(ARRAY[[1, 2], [3, 4], NULL, [NULL], [1, NULL]]) r(arr);
  ```

  | approximate <span>long</span> | exact <span>long</span> |
  | :---------------------------- | :---------------------- |
  | 4                             | 4                       |

  <p><span>Rows: 1</span><span>Execution time: 7.19ms</span></p>
</div>

## Use in Aggregating Indexes

If you use `APPROX_COUNT_DISTINCT` in an aggregating index, every deletion from the base table requires a full table scan to update the index.
Firebolt performs this automatically and transactionally consistent.
However, to achieve better performance in the presence of regular deletions, you can use [`COUNTING_HLL_COUNT_DISTINCT`](/reference-sql/functions-reference/aggregation/counting-hll-count-distinct) instead, where no full table scan is required.