# NQL summarize by

The `summarize by` statement condenses the information into aggregated results grouped by properties or time interval.

## Grouping by property <a href="#nqlsummarizeby-groupingbyproperty" id="nqlsummarizeby-groupingbyproperty"></a>

Enter the field name after `by` to create a breakdown by a property. Enter additional field names separated by a comma to create more breakdown dimensions.

{% hint style="info" %}
The `summarize by` clause does not support grouping by properties with numeric data types such as `days_since_last_seen` (integer), `last_seen` (date time) or `hardware.memory` (byte).
{% endhint %}

### Syntax <a href="#nqlsummarizeby-syntax" id="nqlsummarizeby-syntax"></a>

{% code overflow="wrap" lineNumbers="true" %}

```
...
| summarize <new metric name> = <metric>.<aggregation function> by <field_1>, <field_2> ...
```

{% endcode %}

### Example <a href="#nqlsummarizeby-example" id="nqlsummarizeby-example"></a>

Display the average Confluence backend page load time per device in the last 7 days.

{% code overflow="wrap" lineNumbers="true" %}

```
web.page_views during past 7d
| where application.name == "Confluence"
| summarize backendTime = page_load_time.backend.avg() by device.name
| list device.name, backendTime
| sort backendTime desc
```

{% endcode %}

| Device name     | backendTime |
| --------------- | ----------- |
| device-10d267d2 | 508.2 ms    |
| device-d1d5abc9 | 498.9 ms    |
| device-5117c4c3 | 432.1 ms    |
| device-16834449 | 431.9 ms    |
| device-b634ce84 | 429.4 ms    |
| device-731db075 | 349.8 ms    |
| device-7fb313ef | 293.9 ms    |
| device-a834a720 | 277.6 ms    |
| …               | …           |

## Grouping by period <a href="#nqlsummarizeby-groupingbyperiod" id="nqlsummarizeby-groupingbyperiod"></a>

The `summarize by` statement when used in combination with a time period, groups the metric values into time buckets.

### Syntax <a href="#nqlsummarizeby-syntax.1" id="nqlsummarizeby-syntax.1"></a>

{% code overflow="wrap" lineNumbers="true" %}

```
...
| summarize <new metric name> = <metric>.<aggregation function> by <time period>
```

{% endcode %}

Valid period values are:

* `15 min` `30 min` `45 min` …\
  The value must be a multiple of 15.
* `1 h` `2 h` `3 h` ...\
  The value must be a whole number.
* `1 d` `2 d` `3 d` ...\
  The value must be a whole number.

### Example <a href="#nqlsummarizeby-example.1" id="nqlsummarizeby-example.1"></a>

Display daily number of crashes in the last 7 days in chronological order.

{% code overflow="wrap" lineNumbers="true" %}

```
execution.crashes during past 7d
| summarize total_number_of_crashes = count() by 1d
| sort start_time asc
```

{% endcode %}

| start\_time                   | end\_time                     | bucket\_duration | number\_of\_crashes |
| ----------------------------- | ----------------------------- | ---------------- | ------------------- |
| <p>2021-03-05<br>00:00:00</p> | <p>2021-03-06<br>00:00:00</p> | 1 d              | 758                 |
| <p>2021-03-06<br>00:00:00</p> | <p>2021-03-07<br>00:00:00</p> | 1 d              | 700                 |
| <p>2021-03-07<br>00:00:00</p> | <p>2021-03-08<br>00:00:00</p> | 1 d              | 954                 |
| <p>2021-03-08<br>00:00:00</p> | <p>2021-03-09<br>00:00:00</p> | 1 d              | 493                 |
| <p>2021-03-09<br>00:00:00</p> | <p>2021-03-10<br>00:00:00</p> | 1 d              | 344                 |
| <p>2021-03-10<br>00:00:00</p> | <p>2021-03-11<br>00:00:00</p> | 1 d              | 765                 |
| <p>2021-03-11<br>00:00:00</p> | <p>2021-03-12<br>00:00:00</p> | 1 d              | 857                 |

## Grouping by property and period <a href="#nqlsummarizeby-groupingbypropertyandperiod" id="nqlsummarizeby-groupingbypropertyandperiod"></a>

Combine properties and time period to generate time buckets with additional breakdowns. You can use multiple fields, but only one time period selector. The sequence of items is arbitrary; the time period selector can be positioned anywhere within the list of fields.

### Syntax <a href="#nqlsummarizeby-syntax.2" id="nqlsummarizeby-syntax.2"></a>

{% code overflow="wrap" lineNumbers="true" %}

```
...
| summarize <new metric name> = <metric>.<aggregation function> by <field_1>, <field_2>, ... <time period>, ...
```

{% endcode %}

### Example <a href="#nqlsummarizeby-example.2" id="nqlsummarizeby-example.2"></a>

Display daily number of crashes in the last 30 days broken down by operating system platform and sorted starting from the highest number of crashes.

{% code overflow="wrap" lineNumbers="true" %}

```
execution.crashes during past 30d
| summarize total_number_of_crashes = count() by 1d, device.operating_system.platform 
| sort total_number_of_crashes desc
```

{% endcode %}

| Device platform | start\_time                   | end\_time                     | bucket\_duration | number\_of\_crashes |
| --------------- | ----------------------------- | ----------------------------- | ---------------- | ------------------- |
| Windows         | <p>2021-12-07<br>00:00:00</p> | <p>2021-12-08<br>00:00:00</p> | 1 d              | 690                 |
| Windows         | <p>2021-12-08<br>00:00:00</p> | <p>2021-12-09<br>00:00:00</p> | 1 d              | 533                 |
| macOS           | <p>2021-12-20<br>00:00:00</p> | <p>2021-12-21<br>00:00:00</p> | 1 d              | 511                 |
| Windows         | <p>2021-12-17<br>00:00:00</p> | <p>2021-12-18<br>00:00:00</p> | 1 d              | 493                 |
| Windows         | <p>2021-12-08<br>00:00:00</p> | <p>2021-12-09<br>00:00:00</p> | 1d               | 356                 |
| macOS           | <p>2021-12-20<br>00:00:00</p> | <p>2021-12-21<br>00:00:00</p> | 1d               | 325                 |
| …               | …                             | …                             | …                | …                   |