count_distinct

Use the count\_distinct() function within a comp stage to calculate and return a single count representing the number of unique values found for a specified field over a group of rows.

## Syntax

SQL

comp count\_distinct() \[as ] \[by \[,...]] \[addrawdata = true|false \[as ]]

## Parameters

| Name | Type | Required | Description |
| -------------- | ------------------------------- | -------- | ----------------------------------------------------------------------------------------------------------------------------------- |
| field | string, integer, float, boolean | Yes | The field for which you want to count the number of unique values. |
| alias | string | No | The alias name for the output column, assigned using the as clause. |
| field1, field2 | string, integer, float, boolean | No | The field(s) used to partition the data into distinct groups via the by clause. |
| addrawdata | boolean | No | When set to true, introduces a raw data column into the output that lists the raw data events contributing to the aggregate result. |
| target field | string | No | The alias name for the raw data column when addrawdata is set to true. |

## Returns

The count\_distinct() function returns a single numerical count value representing the number of unique values found for the specified field.

## Usage Notes

* Use count\_distinct() to retrieve the number of unique values in the result set, whereas the count() function retrieves the total number of values.
* The comp stage must always precede an aggregate or approximate aggregate function like count\_distinct().
* New columns generated by the comp stage (including the distinct count itself) are typically appended as the last columns in the result set.
* Any other fields not explicitly included in the by clause or as part of a calculated column will be removed from the result set, including all system fields like \_time.
* When addrawdata is set to true, the query processes up to 50 defined fields and displays up to 100 events.

## Examples

### Example 1: Counting Unique Values of a Specific Field Across the Entire Dataset (No Grouping)

**Goal**: Calculate the total number of unique event\_description values in the sample\_xql\_raw dataset.

**XQL Code**:

SQL

config timeframe = 1d\
\| dataset = sample\_xql\_raw\
\| comp count\_distinct(event\_description) as unique\_event\_descriptions\
\| limit 1

**Explanation**: This query computes the number of unique event\_description values present in the sample\_xql\_raw dataset and names the resulting field unique\_event\_descriptions. Since all event\_description values in sample\_xql\_raw are distinct, the count will be the total number of events.

**Output**:

| unique\_event\_descriptions |
| --------------------------- |
| 10 |

### \*\*Example 2: Counting Unique Values of a Specific Field, Grouped by Another Field

**Goal**: Calculate the number of unique event\_description values separately for successful (true) and unsuccessful (false) events.

**XQL Code**:

SQL

config timeframe = 1d\
\| dataset = sample\_xql\_raw\
\| comp count\_distinct(event\_description) as unique\_descriptions\_by\_status by is\_successful\
\| limit 3

**Explanation**: The query groups records by their is\_successful status and then counts the unique event\_description values for each group, presenting the counts in the unique\_descriptions\_by\_status field. As all event\_description values in sample\_xql\_raw are unique, this effectively counts the number of events in each is\_successful group.

**Output**:

| is\_successful | unique\_descriptions\_by\_status |
| -------------- | -------------------------------- |
| true | 7 |
| false | 3 |

### Example 3: Counting Unique Values of a Derived Field (JSON Extraction and Conversion)

**Goal**: Calculate the count of unique numerical values (derived from code or error\_code fields within simple\_json\_data) after extraction and conversion.

**XQL Code**:

SQL

config timeframe = 1d\
\| dataset = sample\_xql\_raw\
\| alter json\_code\_number = to\_number(coalesce(simple\_json\_data -> code, simple\_json\_data -> error\_code))\
\| comp count\_distinct(json\_code\_number) as unique\_codes\_count\
\| limit 1

**Explanation**: This query first extracts a numerical code or error\_code from simple\_json\_data using syntactic sugar and coalesce(). to\_number() converts the extracted string to a numerical type. Then, count\_distinct() counts how many unique numerical codes were found across the dataset. Based on sample\_xql\_raw, event ID 101 has code 200, and event ID 109 has error\_code 429, resulting in 2 unique values.

**Output**:

| unique\_codes\_count |
| -------------------- |
| 2 |

### Example 4: Counting Unique Numerical Values from an Array Element

**Goal**: Count the number of unique first elements from the numeric\_codes array.

**XQL Code**:

SQL

config timeframe = 1d\
\| dataset = sample\_xql\_raw\
\| alter first\_numeric\_val = arrayindex(numeric\_codes, 0)\
\| comp count\_distinct(first\_numeric\_val) as unique\_first\_numeric\_values\
\| limit 1

**Explanation**: This query uses arrayindex() to access the first numeric code from the numeric\_codes array for each record. The count\_distinct() function then calculates the number of unique non-null values found among these extracted first elements.

**Output**:

| unique\_first\_numeric\_values |
| ------------------------------ |
| 9 |

### Example 5: Counting Unique Values with Raw Data Inclusion (addrawdata=true)

**Goal**: Include the raw events that contribute to each unique count using the addrawdata = true option.

**XQL Code**:

SQL

config timeframe = 1d\
\| dataset = sample\_xql\_raw\
\| comp count\_distinct(event\_id) as distinct\_ids by is\_successful addrawdata = true as raw\_events\_for\_distinct\_count\
\| limit 3

**Explanation**: This query calculates the unique count of event\_id values grouped by is\_successful. Additionally, addrawdata = true generates a new column named raw\_events\_for\_distinct\_count, which contains a JSON representation of the raw events that contributed to each computed distinct count.

**Output**:

| distinct\_ids | is\_successful | raw\_events\_for\_distinct\_count |
| ------------- | -------------- | --------------------------------------------------------------- |
| 7 | true | JSON representation of raw data for 7 unique events (truncated) |
| 3 | false | JSON representation of raw data for 3 unique events (truncated) |

## **Related Articles**

* **Stages**: [comp](../stages/comp), [alter](../stages/alter)
* **Functions**: [count()](count_with_comp_stage), [approx\_count()](approx_count)