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

<AgentInstructions>

## Submitting Feedback

If you encounter incorrect, outdated, or confusing documentation on this page, submit feedback:

POST https://docs.firebolt.io/feedback

```json
{
  "path": "/reference-sql/functions-reference/aggregation/hll-count-distinct",
  "feedback": "Description of the issue"
}
```

Only submit feedback when you have something specific and actionable to report.

</AgentInstructions>

> Reference material for HLL_COUNT_DISTINCT

# HLL_COUNT_DISTINCT

Counts the approximate number of unique or not NULL values, to the precision specified. `HLL_COUNT_DISTINCT` uses the HLL++ algorithm and allows you to control the sketch size set precision.

`HLL_COUNT_DISTINCT` requires less memory than exact aggregate functions, but also introduces statistical uncertainty. The default precision is 12, with a maximum of 20.

<Note>
  Higher precision comes at a memory and performance cost.
</Note>

## Syntax

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
HLL_COUNT_DISTINCT ( <expression> [, <precision> ] )
```

| Parameter      | Description                                                                                                    | Supported input types |
| :------------- | :------------------------------------------------------------------------------------------------------------- | :-------------------- |
| `<expression>` | Valid values for the expression include column names or functions that return a column name.                   | Any type              |
| `<precision>`  | Optional integer value to set precision. If not included, the default precision is 12. Precision range: 12-20. | `INTEGER`, `BIGINT `  |

## Return Type

`BIGINT`

<Note>
  `APPROX_COUNT_DISTINCT(expression)` and `HLL_COUNT_DISTINCT(expression, 17)` return the same results, as `APPROX_COUNT_DISTINCT` uses the HLL algorithm with the default parameter to control the sketch size set to 17.
</Note>

## Return Type

`NUMERIC`

## Example

To understand the difference between `COUNT(DISTINCT pk)` with exact precision enabled, `APPROX_COUNT_DISTNCT(pk)`, and `HLL_COUNT_DISTINCT(pk, <precision>)`, consider a table, `count_test` with 8,388,608 unique `pk` values.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT
	COUNT(DISTINCT pk) as count_distinct,
	APPROX_COUNT_DISTINCT(pk) as approx_count
	HLL_COUNT_DISTINCT(pk, 12) as hll12_count,
	HLL_COUNT_DISTINCT(pk, 20) as hll20_count
FROM
	count_test;
```

**Returns**

Assuming 8,388,608 unique pk values, the previous code example returns the following:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
' +----------------+--------------+-------------+-------------+
' | count_distinct | approx_count | hll12_count | hll20_count |
' +----------------+--------------+-------------+-------------+
' |      8,388,608 |    8,427,387 |   8,667,274 |   8,377,014 |
' +----------------+--------------+-------------+-------------+
```

In the previous result, `approx_count` is using precision 17, `hll12_count` is using precision 12, and `hll20_count` is using precision 20, which is the most precise value.

## Use in Aggregating Indexes

If you use `HLL_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.