> ## 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":"github-light","dark":"github-dark"}}
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:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT
    APPROX_COUNT_DISTINCT(number) as approximate,
    COUNT(DISTINCT number) as exact
FROM
    generate_series(1, 1000) r(number);
```

**Returns**:

| approximate | exact |
| :---------- | :---- |
| 1,000       | 1,000 |

`NULL` values do not change the result of `APPROX_COUNT_DISTINCT`:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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);
```

**Returns**:

| approximate | exact |
| :---------- | :---- |
| 1,000       | 1,000 |

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

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT
    APPROX_COUNT_DISTINCT(number) as approximate,
    COUNT(DISTINCT number) as exact
FROM
    generate_series(1, 50000) r(number);
```

**Returns**:

| approximate | exact  |
| :---------- | :----- |
| 50,160      | 50,000 |

`APPROX_COUNT_DISTINCT` also works for compound types:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT 
    APPROX_COUNT_DISTINCT(arr) as approximate,
    COUNT(DISTINCT arr) as exact
FROM 
    unnest([[1, 2], [3, 4], NULL, [NULL], [1, NULL]]) r(arr)
```

**Returns**:

| approximate | exact |
| :---------- | :---- |
| 4           | 4     |

## 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.