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

# APPROX_COUNT_DISTINCT

> Estimates the number of distinct non-NULL input values.

<div className="e6-meta"><span className="e6-eyebrow e6-eyebrow--quiet">Aggregate · Window</span></div>

NULL values are ignored.

## Syntax

A window function keeps each input row and adds a result for that row. `PARTITION BY` groups rows with matching values; without it, all input rows form one group.

### Aggregate

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
approx_count_distinct(value)
```

### Window

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
approx_count_distinct(value) OVER ([PARTITION BY key] [ORDER BY ordering_key] [ROWS frame])
```

* Use a frame that includes all rows in the group or starts at the first row (`UNBOUNDED PRECEDING`). Sliding frames that drop earlier rows are not supported. RANGE frames with a numeric or time offset are not supported.

## Arguments

### Aggregate and window

| Name | Type | Required | Description |
| - | - | - | - |
| value | BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL(p, s), STRING, BINARY, DATE, TIME or TIMESTAMP | Yes | Values whose distinct count is estimated. For DECIMAL(p, s), p is precision (total digits) and s is scale (digits after the decimal point). |

## Returns

* Type: BIGINT
* NULL behavior: NULL values are ignored.
* Empty input: Returns zero on empty input.

## Properties

| Property | Value |
| - | - |
| Deterministic | Yes |
| Supports window usage | Partial (Use frames starting at the first row. See Syntax.) |
| Supports window frames | Partial (Use frames starting at the first row. See Syntax.) |
| Exact result | No |

* Approximation bound: Standard error is 0.8125%. This describes typical statistical variation, not a guaranteed maximum error.

## Usage notes

* TINYINT and SMALLINT inputs return an exact count. Counts for other supported types are estimates.
* All floating-point NaN values count as one distinct value. Positive and negative zero also count as the same value.
* INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.

## Examples

### Count distinct non-NULL values

There are two distinct non-NULL values: 1 and 2. SMALLINT inputs are counted exactly.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH t(v) AS (VALUES (CAST(1 AS SMALLINT)), (CAST(2 AS SMALLINT)), (CAST(2 AS SMALLINT)), (CAST(NULL AS SMALLINT))) SELECT approx_count_distinct(v) AS result FROM t;
```

| result |
| - |
| 2 |

### Count distinct strings per group

These small groups produce the counts shown; the function estimates counts for larger inputs.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH sample(grp, v) AS (VALUES (1, 'red'), (1, 'red'), (1, 'blue'), (2, CAST(NULL AS VARCHAR)))
SELECT grp, approx_count_distinct(v) AS distinct_values
FROM sample GROUP BY grp ORDER BY grp;
```

| grp | distinct\_values |
| - | - |
| 1 | 2 |
| 2 | 0 |

## Limitations

* The error level is fixed. An argument to configure relative standard deviation is not accepted.
* Sliding windows that drop earlier rows are not supported. Start the frame at the first row in the group.
* ARRAY, MAP, and STRUCT inputs are not supported.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.