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)