# Aggregating data

Copy as Markdown[Open in ChatGPT](https://chatgpt.com/?q=Read%20https%3A%2F%2Fdocs-docusaurus.kinsta.page%2Fdataprime%2Fuser-guide%2Fusing-dataprime%2Faggregation.md%20and%20help%20me%20with%20my%20question%20about%20this%20Coralogix%20documentation%20page.)[Open in Claude](https://claude.ai/new?q=Read%20https%3A%2F%2Fdocs-docusaurus.kinsta.page%2Fdataprime%2Fuser-guide%2Fusing-dataprime%2Faggregation.md%20and%20help%20me%20with%20my%20question%20about%20this%20Coralogix%20documentation%20page.)

## Goal[​](#goal "Direct link to Goal")

By the end of this guide you should be able to use [`aggregate`](https://docs-docusaurus.kinsta.page/dataprime/language-reference/commands-reference/aggregate/.md), [`groupby`](https://docs-docusaurus.kinsta.page/dataprime/language-reference/commands-reference/groupby/.md), [`avg`](https://docs-docusaurus.kinsta.page/dataprime/language-reference/functions-reference/aggregation/avg/.md), [`count_if`](https://docs-docusaurus.kinsta.page/dataprime/language-reference/functions-reference/aggregation/count_if/.md), and other functions to calculate metrics such as averages, totals, and conditional counts from your logs or traces.

## Why it matters[​](#why-it-matters "Direct link to Why it matters")

Understanding what your systems are doing at scale often requires more than just viewing raw logs, it requires summarization. Aggregations let you answer questions like “Which endpoints are the slowest?”, “How many error traces exceeded 1s?” or “What is the average request size per service?” without manually inspecting individual events.

## Count all logs or traces[​](#count-all-logs-or-traces "Direct link to Count all logs or traces")

### Description[​](#description "Direct link to Description")

Use [`count()`](https://docs-docusaurus.kinsta.page/dataprime/language-reference/commands-reference/count/.md) to return the total number of documents in the dataset or current time range. This is the simplest and fastest way to verify volume before applying filters or grouping.

### Syntax[​](#syntax "Direct link to Syntax")

```
count(): number
```

### Example: Count total number of trace spans[​](#example-count-total-number-of-trace-spans "Direct link to Example: Count total number of trace spans")

#### Sample data[​](#sample-data "Direct link to Sample data")

```
{ "trace_id": "t1", "duration": 500 }

{ "trace_id": "t2", "duration": 620 }

{ "trace_id": "t3", "duration": 80 }
```

#### Query[​](#query "Direct link to Query")

```
source spans

| aggregate count() as total_spans
```

#### Result[​](#result "Direct link to Result")

| total\_spans |
| ------------ |
| 3            |

***

## Group by a field and calculate an average[​](#group-by-a-field-and-calculate-an-average "Direct link to Group by a field and calculate an average")

### Description[​](#description-1 "Direct link to Description")

Use [`groupby`](https://docs-docusaurus.kinsta.page/dataprime/language-reference/commands-reference/groupby/.md) with [`avg()`](https://docs-docusaurus.kinsta.page/dataprime/language-reference/functions-reference/aggregation/avg/.md) to measure how a numerical value (e.g., duration, size, latency) varies by category.

### Syntax[​](#syntax-1 "Direct link to Syntax")

```
groupby <key> aggregate avg(<number_field>) as <alias>
```

### Example: Compute average span duration per region[​](#example-compute-average-span-duration-per-region "Direct link to Example: Compute average span duration per region")

#### Sample data[​](#sample-data-1 "Direct link to Sample data")

```
{ "cloud_region": "eu-north-1", "duration": 500 }

{ "cloud_region": "eu-north-1", "duration": 654 }

{ "cloud_region": "eu-west-1", "duration": 2100 }

{ "cloud_region": "us-east-2", "duration": 150 }
```

#### Query[​](#query-1 "Direct link to Query")

```
source spans

| groupby cloud_region aggregate avg(duration) as avg_duration
```

#### Result[​](#result-1 "Direct link to Result")

| cloud\_region | avg\_duration |
| ------------- | ------------- |
| eu-north-1    | 577.0         |
| eu-west-1     | 2100.0        |
| us-east-2     | 150.0         |

This highlights regional performance differences. You can use this pattern to monitor latency by path, status code, or service.

***

## Add multiple aggregations at once[​](#add-multiple-aggregations-at-once "Direct link to Add multiple aggregations at once")

### Description[​](#description-2 "Direct link to Description")

You can combine multiple aggregation functions in a single query to analyze different metrics simultaneously.

### Example: Measure average, max, and count of spans per region[​](#example-measure-average-max-and-count-of-spans-per-region "Direct link to Example: Measure average, max, and count of spans per region")

#### Sample data[​](#sample-data-2 "Direct link to Sample data")

```
{ "cloud_region": "eu-north-1", "duration": 500 }

{ "cloud_region": "eu-north-1", "duration": 900 }

{ "cloud_region": "eu-west-1", "duration": 2100 }

{ "cloud_region": "us-east-2", "duration": 150 }
```

#### Query[​](#query-2 "Direct link to Query")

```
source spans

| groupby cloud_region aggregate

    avg(duration) as avg_duration,

    max(duration) as max_duration,

    count() as request_count
```

#### Result[​](#result-2 "Direct link to Result")

| cloud\_region | avg\_duration | max\_duration | request\_count |
| ------------- | ------------- | ------------- | -------------- |
| eu-north-1    | 700.0         | 900           | 2              |
| eu-west-1     | 2100.0        | 2100          | 1              |
| us-east-2     | 150.0         | 150           | 1              |

This approach is useful for analyzing scale and performance in the same query.

***

## Filter before aggregating[​](#filter-before-aggregating "Direct link to Filter before aggregating")

### Description[​](#description-3 "Direct link to Description")

Use [`filter`](https://docs-docusaurus.kinsta.page/dataprime/language-reference/commands-reference/filter/.md) to narrow down the dataset *before* aggregating. This improves performance and ensures your calculations reflect only relevant data.

### Example: Count only slow requests per region[​](#example-count-only-slow-requests-per-region "Direct link to Example: Count only slow requests per region")

#### Sample data[​](#sample-data-3 "Direct link to Sample data")

```
{ "cloud_region": "eu-north-1", "duration": 900 }

{ "cloud_region": "eu-north-1", "duration": 300 }

{ "cloud_region": "us-east-2", "duration": 800 }
```

#### Query[​](#query-3 "Direct link to Query")

```
source spans

| filter duration > 500

| groupby cloud_region aggregate count() as slow_request_count
```

#### Result[​](#result-3 "Direct link to Result")

| cloud\_region | slow\_request\_count |
| ------------- | -------------------- |
| eu-north-1    | 1                    |
| us-east-2     | 1                    |

The filter ensures that only spans above 500ms are included in the aggregation.

***

## Use `count_if` for conditional metrics[​](#use-count_if-for-conditional-metrics "Direct link to use-count_if-for-conditional-metrics")

### Description[​](#description-4 "Direct link to Description")

Use [`count_if()`](https://docs-docusaurus.kinsta.page/dataprime/language-reference/functions-reference/aggregation/count_if/.md) to count how many documents meet a specific condition within a group.

### Syntax[​](#syntax-2 "Direct link to Syntax")

```
count_if(condition: bool): number
```

### Example: Compare total and slow spans per region[​](#example-compare-total-and-slow-spans-per-region "Direct link to Example: Compare total and slow spans per region")

#### Sample data[​](#sample-data-4 "Direct link to Sample data")

```
{ "cloud_region": "eu-west-1", "duration": 700 }

{ "cloud_region": "eu-west-1", "duration": 400 }

{ "cloud_region": "us-east-2", "duration": 300 }

{ "cloud_region": "us-east-2", "duration": 1200 }
```

#### Query[​](#query-4 "Direct link to Query")

```
source spans

| groupby cloud_region aggregate

    count() as total_requests,

    count_if(duration > 500) as slow_requests
```

#### Result[​](#result-4 "Direct link to Result")

| cloud\_region | total\_requests | slow\_requests |
| ------------- | --------------- | -------------- |
| eu-west-1     | 2               | 1              |
| us-east-2     | 2               | 1              |

This lets you track the proportion of problematic spans per group.

***

## Group by calculated severity level[​](#group-by-calculated-severity-level "Direct link to Group by calculated severity level")

### Description[​](#description-5 "Direct link to Description")

You can dynamically group documents using logic via a [`case`](https://docs-docusaurus.kinsta.page/dataprime/language-reference/functions-reference/cases/case/.md) expression. This is useful for creating buckets like "fast", "slow", and "critical".

### Syntax[​](#syntax-3 "Direct link to Syntax")

```
groupby case {

  <condition1> -> <label1>,

  <condition2> -> <label2>,

  _ -> <default_label>

} as <alias>

aggregate count() as <alias>
```

### Example: Group spans into latency tiers[​](#example-group-spans-into-latency-tiers "Direct link to Example: Group spans into latency tiers")

#### Sample data[​](#sample-data-5 "Direct link to Sample data")

```
{ "duration": 1200 }

{ "duration": 800 }

{ "duration": 300 }
```

#### Query[​](#query-5 "Direct link to Query")

```
source spans

| groupby case {

    duration > 1000 -> 'super slow',

    duration > 500 -> 'slow',

    _ -> 'ok'

  } as latency_level

| aggregate count() as request_count
```

#### Result[​](#result-5 "Direct link to Result")

| latency\_level | request\_count |
| -------------- | -------------- |
| super slow     | 1              |
| slow           | 2              |
| ok             | 8              |

***

## Aggregate over spans (traces)[​](#aggregate-over-spans-traces "Direct link to Aggregate over spans (traces)")

### Description[​](#description-6 "Direct link to Description")

You can aggregate directly on spans using basic metrics like count and max. This is often used to get an overview of tracing volume and performance.

### Example: Count spans and find longest one[​](#example-count-spans-and-find-longest-one "Direct link to Example: Count spans and find longest one")

#### Sample data[​](#sample-data-6 "Direct link to Sample data")

```
{ "duration": 8000 }

{ "duration": 20000 }

{ "duration": 1000 }
```

#### Query[​](#query-6 "Direct link to Query")

```
source spans

| aggregate

    count() as trace_count,

    max(duration) as max_trace_duration
```

#### Result[​](#result-6 "Direct link to Result")

| trace\_count | max\_trace\_duration |
| ------------ | -------------------- |
| 3            | 20000                |

This is helpful for validating trace ingestion and spotting extreme latency outliers.

***

## Common pitfalls or gotchas[​](#common-pitfalls-or-gotchas "Direct link to Common pitfalls or gotchas")

* `groupby` removes all fields not explicitly included. Add fields as grouping keys or aggregations if you want to preserve them.
* Aggregations like `avg`, `count_if`, and `max` ignore `null` values. Make sure fields you're aggregating on are populated.
* You can only group by scalar values—arrays and objects are not supported unless flattened or transformed first.