ES|QL STATS command

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

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

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.

expressionX

An expression that computes an aggregated value.

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.

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:

... | STATS ... WHERE <condition> ...
		

... | WHERE <condition> | STATS ...
		

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 are supported:

• ABSENT
• AVG
• COUNT
• COUNT_DISTINCT
• MAX
• MEDIAN
• MEDIAN_ABSOLUTE_DEVIATION
• MIN
• PERCENTILE
• PRESENT
• SAMPLE
• ST_CENTROID_AGG
• ST_EXTENT_AGG
• STD_DEV
• SUM
• TOP
• VALUES
• VARIANCE
• WEIGHTED_AVG
• FIRST
• LAST

When STATS is used under the TS source command, time series aggregation functions are also supported.

The following grouping functions are supported:

• BUCKET
• TBUCKET
• CATEGORIZE

The following examples demonstrate common STATS patterns.

Combine an aggregation with BY to compute a value for each group:

FROM employees
| STATS count = COUNT(emp_no) BY languages
| SORT languages
		

Omitting BY returns one row with the aggregations applied over the entire dataset:

FROM employees
| STATS avg_lang = AVG(languages)
		

Separate multiple aggregations with commas to compute them in a single pass:

FROM employees
| STATS avg_lang = AVG(languages), max_lang = MAX(languages)
		

Use per-aggregation WHERE to compute conditional metrics from the same dataset in a single pass:

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
		

Filtered and unfiltered aggregations can be freely mixed. Grouping is also optional:

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(*)
		

1. thousands

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:

FROM employees
| STATS `count` = COUNT(*) WHERE languages < 3 BY languages
		

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:

FROM employees
| WHERE languages < 3
| STATS `count` = COUNT(*) BY languages
		

Separate multiple grouping expressions with a comma:

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
		

If the grouping key is multivalued then the input row is in all groups:

ROW price = 10, color = ["blue", "pink", "yellow"]
| STATS SUM(price) BY color
		

If all the grouping keys are multivalued then the input row is in all groups:

ROW price = 10, color = ["blue", "pink", "yellow"], size = ["s", "m", "l"]
| STATS SUM(price) BY color, size
		

The input ROW is in all groups. The entire row. All the values. Even group keys. That means that:

ROW color = ["blue", "pink", "yellow"]
| STATS VALUES(color) BY color
		

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:

ROW color = ["blue", "pink", "yellow"]
| MV_EXPAND color
| STATS VALUES(color) BY color
		

Refer to elasticsearch/issues/134792 for an even more in depth explanation.

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:

FROM employees
| STATS avg_salary_change = ROUND(AVG(MV_AVG(salary_change)), 10)
		

Grouping expressions aren't limited to column references — any expression works. For example, group by a derived value using LEFT:

FROM employees
| STATS my_count = COUNT() BY LEFT(last_name, 1)
| SORT `LEFT(last_name, 1)`
		

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):

FROM employees
| STATS AVG(salary)
		

Because this name contains special characters, it needs to be quoted with backticks (```) when using it in subsequent commands:

FROM employees
| STATS AVG(salary)
| EVAL avg_salary_rounded = ROUND(`AVG(salary)`)