> For the complete documentation index, see [llms.txt](https://cortex-docs.paloaltonetworks.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/functions/count_distinct.md).

# 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](/xql-command-reference-guide/readme/stages/comp.md), [alter](/xql-command-reference-guide/readme/stages/alter.md)
* **Functions**: [count()](/xql-command-reference-guide/readme/functions/count_with_comp_stage.md), [approx\_count()](/xql-command-reference-guide/readme/functions/approx_count.md)


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/functions/count_distinct.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.