---
title: ES|QL STATS command
description: 
url: https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/commands/stats-by
products:
  - Elasticsearch
---

# ES|QL STATS command
<applies-to>
  - Elastic Cloud Serverless: Generally available
  - Elastic Stack: Generally available
</applies-to>

The `STATS` processing command groups rows according to a common value
and calculates one or more aggregated values over the grouped rows.

## Syntax

```esql
STATS [column1 =] expression1 [WHERE boolean_expression1][,
      ...,
      [columnN =] expressionN [WHERE boolean_expressionN]]
      [BY grouping_expression1[, ..., grouping_expressionN]]
```


## Parameters

<definitions>
  <definition term="columnX">
    The name by which the aggregated value is returned. If omitted, the name is
    equal to the corresponding expression (`expressionX`).
    If multiple columns have the same name, all but the rightmost column with this
    name will be ignored.
  </definition>
  <definition term="expressionX">
    An expression that computes an aggregated value.
  </definition>
  <definition term="grouping_expressionX">
    An expression that outputs the values to group by.
    If its name coincides with one of the computed columns, that column will be ignored.
  </definition>
  <definition term="boolean_expressionX">
    The condition that must be met for a row to be included in the evaluation of
    `expressionX`. Has no effect on `grouping_expressionX` or other aggregation
    expressions. Consequently, the following are _not_ equivalent:
    ```esql
    ... | STATS ... WHERE <condition> ...
    ```
    ```esql
    ... | WHERE <condition> | STATS ...
    ```
  </definition>
</definitions>

<note>
  Individual `null` values are skipped when computing aggregations.
</note>


## Description

The `STATS` processing command groups rows according to a common value
and calculates one or more aggregated values over the grouped rows. For the
calculation of each aggregated value, the rows in a group can be filtered with
`WHERE`. If `BY` is omitted, the output table contains exactly one row with
the aggregations applied over the entire dataset.
The following [aggregation functions](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/aggregation-functions) are supported:
- [`ABSENT`](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/aggregation-functions/absent) <applies-to>Elastic Stack: Generally available since 9.2</applies-to>
- [`AVG`](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/aggregation-functions/avg)
- [`COUNT`](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/aggregation-functions/count)
- [`COUNT_DISTINCT`](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/aggregation-functions/count_distinct)
- [`FIRST`](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/aggregation-functions/first) <applies-to>Elastic Stack: Planned</applies-to> <applies-to>Elastic Cloud Serverless: Preview</applies-to>
- [`LAST`](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/aggregation-functions/last) <applies-to>Elastic Stack: Planned</applies-to> <applies-to>Elastic Cloud Serverless: Preview</applies-to>
- [`MAX`](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/aggregation-functions/max)
- [`MEDIAN`](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/aggregation-functions/median)
- [`MEDIAN_ABSOLUTE_DEVIATION`](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/aggregation-functions/median_absolute_deviation)
- [`MIN`](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/aggregation-functions/min)
- [`PERCENTILE`](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/aggregation-functions/percentile)
- [`PRESENT`](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/aggregation-functions/present) <applies-to>Elastic Stack: Generally available since 9.2</applies-to>
- [`SAMPLE`](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/aggregation-functions/sample) <applies-to>Elastic Stack: Generally available since 9.1</applies-to>
- [`SPARKLINE`](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/aggregation-functions/sparkline) <applies-to>Elastic Stack: Preview</applies-to> <applies-to>Elastic Cloud Serverless: Preview</applies-to>
- [`ST_CENTROID_AGG`](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/aggregation-functions/st_centroid_agg) <applies-to>Elastic Stack: Preview</applies-to> <applies-to>Elastic Cloud Serverless: Preview</applies-to>
- [`ST_EXTENT_AGG`](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/aggregation-functions/st_extent_agg) <applies-to>Elastic Stack: Preview</applies-to> <applies-to>Elastic Cloud Serverless: Preview</applies-to>
- [`STD_DEV`](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/aggregation-functions/std_dev)
- [`SUM`](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/aggregation-functions/sum)
- [`TOP`](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/aggregation-functions/top)
- [`VALUES`](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/aggregation-functions/values) <applies-to>Elastic Stack: Preview</applies-to> <applies-to>Elastic Cloud Serverless: Preview</applies-to>
- [`VARIANCE`](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/aggregation-functions/variance)
- [`WEIGHTED_AVG`](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/aggregation-functions/weighted_avg)

When `STATS` is used under the [`TS`](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/commands/ts) source command,
[time series aggregation functions](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/time-series-aggregation-functions)
are also supported.
The following [grouping functions](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/grouping-functions) are supported:
- [`BUCKET`](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/grouping-functions/bucket)
- [`CATEGORIZE`](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/grouping-functions/categorize) <applies-to>Elastic Stack: Preview since 9.0</applies-to> <applies-to>Elastic Stack: Generally available since 9.1</applies-to>
- [`TBUCKET`](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/grouping-functions/tbucket) <applies-to>Elastic Stack: Generally available since 9.2</applies-to>

<note>
  `STATS` without any groups is much much faster than adding a group.
</note>

<note>
  Grouping on a single expression is currently much more optimized than grouping
  on many expressions. In some tests we have seen grouping on a single `keyword`
  column to be five times faster than grouping on two `keyword` columns. Do
  not try to work around this by combining the two columns together with
  something like [`CONCAT`](https://www.elastic.co/elastic/docs-builder/docs/3016/reference/query-languages/esql/functions-operators/string-functions/concat)
  and then grouping - that is not going to be faster.
</note>


## Examples

The following examples demonstrate common `STATS` patterns.

### Group by column

Combine an aggregation with `BY` to compute a value for each group:
```esql
FROM employees
| STATS count = COUNT(emp_no) BY languages
| SORT languages
```


| count:long | languages:integer |
|------------|-------------------|
| 15         | 1                 |
| 19         | 2                 |
| 17         | 3                 |
| 18         | 4                 |
| 21         | 5                 |
| 10         | null              |


### Aggregate without grouping

Omitting `BY` returns one row with the aggregations applied over the entire
dataset:
```esql
FROM employees
| STATS avg_lang = AVG(languages)
```


| avg_lang:double    |
|--------------------|
| 3.1222222222222222 |


### Calculate multiple values

Separate multiple aggregations with commas to compute them in a single pass:
```esql
FROM employees
| STATS avg_lang = AVG(languages), max_lang = MAX(languages)
```


| avg_lang:double    | max_lang:integer |
|--------------------|------------------|
| 3.1222222222222222 | 5                |


### Filter aggregations with WHERE

Use per-aggregation `WHERE` to compute conditional metrics from the same
dataset in a single pass:
```esql
FROM employees
| STATS avg50s = AVG(salary)::LONG WHERE birth_date < "1960-01-01",
        avg60s = AVG(salary)::LONG WHERE birth_date >= "1960-01-01"
        BY gender
| SORT gender
```


| avg50s:long | avg60s:long | gender:keyword |
|-------------|-------------|----------------|
| 55462       | 46637       | F              |
| 48279       | 44879       | M              |


### Mix filtered and unfiltered aggregations

Filtered and unfiltered aggregations can be freely mixed. Grouping is also
optional:
```esql
FROM employees
| EVAL Ks = salary / 1000
| STATS under_40K = COUNT(*) WHERE Ks < 40,
        inbetween = COUNT(*) WHERE 40 <= Ks AND Ks < 60,
        over_60K  = COUNT(*) WHERE 60 <= Ks,
        total     = COUNT(*)
```


| under_40K:long | inbetween:long | over_60K:long | total:long |
|----------------|----------------|---------------|------------|
| 36             | 39             | 25            | 100        |


### Filter on the grouping key

The `WHERE` clause can also filter on the grouping key. The group itself will
still appear in the output, but with a default value for the aggregation:
```esql
FROM employees
| STATS `count` = COUNT(*) WHERE languages < 3 BY languages
```


| count:long | languages:integer |
|------------|-------------------|
| 15         | 1                 |
| 19         | 2                 |
| 0          | 3                 |
| 0          | 4                 |
| 0          | 5                 |
| 0          | null              |

Compare this to filtering with `WHERE` before `STATS`, where rows are excluded
before grouping, so non-matching groups don't appear in the output at all:
```esql
FROM employees
| WHERE languages < 3
| STATS `count` = COUNT(*) BY languages
```


| count:long | languages:integer |
|------------|-------------------|
| 15         | 1                 |
| 19         | 2                 |


### Group by multiple values

Separate multiple grouping expressions with a comma:
```esql
FROM employees
| EVAL hired = DATE_FORMAT("yyyy", hire_date)
| STATS avg_salary = AVG(salary) BY hired, languages.long
| EVAL avg_salary = ROUND(avg_salary)
| SORT hired, languages.long
```



### Multivalued inputs

If the grouping key is multivalued then the input row is in all groups:
```esql
ROW price = 10, color = ["blue", "pink", "yellow"]
| STATS SUM(price) BY color
```


| SUM(price):long | color:keyword |
|-----------------|---------------|
| 10              | blue          |
| 10              | pink          |
| 10              | yellow        |

If all the grouping keys are multivalued then the input row is in all groups:
```esql
ROW price = 10, color = ["blue", "pink", "yellow"], size = ["s", "m", "l"]
| STATS SUM(price) BY color, size
```


| SUM(price):long | color:keyword | size:keyword |
|-----------------|---------------|--------------|
| 10              | blue          | l            |
| 10              | blue          | m            |
| 10              | blue          | s            |
| 10              | pink          | l            |
| 10              | pink          | m            |
| 10              | pink          | s            |
| 10              | yellow        | l            |
| 10              | yellow        | m            |
| 10              | yellow        | s            |

The input **ROW** is in all groups. The entire row. All the values. Even group
keys. That means that:
```esql
ROW color = ["blue", "pink", "yellow"]
| STATS VALUES(color) BY color
```


| VALUES(color):keyword | color:keyword |
|-----------------------|---------------|
| [blue, pink, yellow]  | blue          |
| [blue, pink, yellow]  | pink          |
| [blue, pink, yellow]  | yellow        |

The `VALUES` function above sees the whole row - all of the values of the group
key. If you want to send the group key to the function then `MV_EXPAND` first:
```esql
ROW color = ["blue", "pink", "yellow"]
| MV_EXPAND color
| STATS VALUES(color) BY color
```


| VALUES(color):keyword | color:keyword |
|-----------------------|---------------|
| blue                  | blue          |
| pink                  | pink          |
| yellow                | yellow        |

Refer to [elasticsearch/issues/134792](https://github.com/elastic/elasticsearch/issues/134792#issuecomment-3361168090)
for an even more in depth explanation.

### Multivalue functions

Both aggregation and grouping expressions accept other functions, which is
useful for using `STATS` on multivalue columns. For example, to calculate the
average salary change, use `MV_AVG` to first average the multiple values per
employee, then pass the result to `AVG`:
```esql
FROM employees
| STATS avg_salary_change = ROUND(AVG(MV_AVG(salary_change)), 10)
```


| avg_salary_change:double |
|--------------------------|
| 1.3904535865             |

Grouping expressions aren't limited to column references: any expression
works. For example, group by a derived value using `LEFT`:
```esql
FROM employees
| STATS my_count = COUNT() BY LEFT(last_name, 1)
| SORT `LEFT(last_name, 1)`
```


| my_count:long | LEFT(last_name, 1):keyword |
|---------------|----------------------------|
| 2             | A                          |
| 11            | B                          |
| 5             | C                          |
| 5             | D                          |
| 2             | E                          |
| 4             | F                          |
| 4             | G                          |
| 6             | H                          |
| 2             | J                          |
| 3             | K                          |
| 5             | L                          |
| 12            | M                          |
| 4             | N                          |
| 1             | O                          |
| 7             | P                          |
| 5             | R                          |
| 13            | S                          |
| 4             | T                          |
| 2             | W                          |
| 3             | Z                          |


### Output column naming

Specifying the output column name is optional. If not specified, the new column
name is equal to the expression. The following query returns a column named
`AVG(salary)`:
```esql
FROM employees
| STATS AVG(salary)
```


| AVG(salary):double |
|--------------------|
| 48248.55           |

Because this name contains special characters,
[it needs to be quoted](/elastic/docs-builder/docs/3016/reference/query-languages/esql/esql-syntax#esql-identifiers)
with backticks (```) when using it in subsequent commands:
```esql
FROM employees
| STATS AVG(salary)
| EVAL avg_salary_rounded = ROUND(`AVG(salary)`)
```


| AVG(salary):double | avg_salary_rounded:double |
|--------------------|---------------------------|
| 48248.55           | 48249.0                   |