› › ›

Aggregate Functions

Functions for computing a single result from a set of input values. Elasticsearch SQL supports aggregate functions only alongside grouping (implicit or explicit).

General Purpose

`AVG`

Synopsis:

AVG(numeric_field)

Input:

numeric field. If this field contains only null values, the function returns null. Otherwise, the function ignores null values in this field.

Output: double numeric value

Description: Returns the Average (arithmetic mean) of input values.

SELECT AVG(salary) AS avg FROM emp;

      avg
---------------
48248.55

SELECT AVG(salary / 12.0) AS avg FROM emp;

      avg
---------------
4020.7125

`COUNT`

Synopsis:

COUNT(expression)

Input:

a field name, wildcard (*) or any numeric value. For COUNT(*) or COUNT(<literal>), all values are considered, including null or missing ones. For COUNT(<field_name>), null values are not considered.

Output: numeric value

Description: Returns the total number (count) of input values.

SELECT COUNT(*) AS count FROM emp;

     count
---------------
100

`COUNT(ALL)`

Synopsis:

COUNT(ALL field_name)

Input:

a field name. If this field contains only null values, the function returns null. Otherwise, the function ignores null values in this field.

Output: numeric value

Description: Returns the total number (count) of all non-null input values. COUNT(<field_name>) and COUNT(ALL <field_name>) are equivalent.

SELECT COUNT(ALL last_name) AS count_all, COUNT(DISTINCT last_name) count_distinct FROM emp;

   count_all   |  count_distinct
---------------+------------------
100            |96

SELECT COUNT(ALL CASE WHEN languages IS NULL THEN -1 ELSE languages END) AS count_all, COUNT(DISTINCT CASE WHEN languages IS NULL THEN -1 ELSE languages END) count_distinct FROM emp;

   count_all   |  count_distinct
---------------+---------------
100            |6

`COUNT(DISTINCT)`

Synopsis:

COUNT(DISTINCT field_name)

Input:

a field name

Output: numeric value. If this field contains only null values, the function returns null. Otherwise, the function ignores null values in this field.

Description: Returns the total number of distinct non-null values in input values.

SELECT COUNT(DISTINCT hire_date) unique_hires, COUNT(hire_date) AS hires FROM emp;

  unique_hires  |     hires
----------------+---------------
99              |100

SELECT COUNT(DISTINCT DATE_TRUNC('YEAR', hire_date)) unique_hires, COUNT(DATE_TRUNC('YEAR', hire_date)) AS hires FROM emp;

 unique_hires  |     hires
---------------+---------------
14             |100

`FIRST/FIRST_VALUE`

Synopsis:

FIRST(
    field_name               
    [, ordering_field_name])

Input:

	target field for the aggregation
	optional field used for ordering

Output: same type as the input

Description: Returns the first non-null value (if such exists) of the field_name input column sorted by the ordering_field_name column. If ordering_field_name is not provided, only the field_name column is used for the sorting. E.g.:

a	b
100	1
200	1
1	2
2	2
10	null
20	null
null	null

SELECT FIRST(a) FROM t

will result in:

FIRST(a)

and

SELECT FIRST(a, b) FROM t

will result in:

FIRST(a, b)

100

SELECT FIRST(first_name) FROM emp;

   FIRST(first_name)
--------------------
Alejandro

SELECT gender, FIRST(first_name) FROM emp GROUP BY gender ORDER BY gender;

   gender   |   FIRST(first_name)
------------+--------------------
null        |   Berni
F           |   Alejandro
M           |   Amabile

SELECT FIRST(first_name, birth_date) FROM emp;

   FIRST(first_name, birth_date)
--------------------------------
Remzi

SELECT gender, FIRST(first_name, birth_date) FROM emp GROUP BY gender ORDER BY gender;

    gender    |   FIRST(first_name, birth_date)
--------------+--------------------------------
null          |   Lillian
F             |   Sumant
M             |   Remzi

FIRST_VALUE is a name alias and can be used instead of FIRST, e.g.:

SELECT gender, FIRST_VALUE(first_name, birth_date) FROM emp GROUP BY gender ORDER BY gender;

    gender    |   FIRST_VALUE(first_name, birth_date)
--------------+--------------------------------------
null          |   Lillian
F             |   Sumant
M             |   Remzi

SELECT gender, FIRST_VALUE(SUBSTRING(first_name, 2, 6), birth_date) AS "first" FROM emp GROUP BY gender ORDER BY gender;

    gender     |     first
---------------+---------------
null           |illian
F              |umant
M              |emzi

FIRST cannot be used in a HAVING clause.

FIRST cannot be used with columns of type text unless the field is also saved as a keyword.

`LAST/LAST_VALUE`

Synopsis:

LAST(
    field_name               
    [, ordering_field_name])

Input:

	target field for the aggregation
	optional field used for ordering

Output: same type as the input

Description: It’s the inverse of FIRST/FIRST_VALUE. Returns the last non-null value (if such exists) of the field_name input column sorted descending by the ordering_field_name column. If ordering_field_name is not provided, only the field_name column is used for the sorting. E.g.:

a	b
10	1
20	1
1	2
2	2
100	null
200	null
null	null

SELECT LAST(a) FROM t

will result in:

LAST(a)

200

and

SELECT LAST(a, b) FROM t

will result in:

LAST(a, b)

SELECT LAST(first_name) FROM emp;

   LAST(first_name)
-------------------
Zvonko

SELECT gender, LAST(first_name) FROM emp GROUP BY gender ORDER BY gender;

   gender   |   LAST(first_name)
------------+-------------------
null        |   Patricio
F           |   Xinglin
M           |   Zvonko

SELECT LAST(first_name, birth_date) FROM emp;

   LAST(first_name, birth_date)
-------------------------------
Hilari

SELECT gender, LAST(first_name, birth_date) FROM emp GROUP BY gender ORDER BY gender;

   gender  |   LAST(first_name, birth_date)
-----------+-------------------------------
null       |   Eberhardt
F          |   Valdiodio
M          |   Hilari

LAST_VALUE is a name alias and can be used instead of LAST, e.g.:

SELECT gender, LAST_VALUE(first_name, birth_date) FROM emp GROUP BY gender ORDER BY gender;

   gender  |   LAST_VALUE(first_name, birth_date)
-----------+-------------------------------------
null       |   Eberhardt
F          |   Valdiodio
M          |   Hilari

SELECT gender, LAST_VALUE(SUBSTRING(first_name, 3, 8), birth_date) AS "last" FROM emp GROUP BY gender ORDER BY gender;

    gender     |     last
---------------+---------------
null           |erhardt
F              |ldiodio
M              |lari

LAST cannot be used in HAVING clause.

LAST cannot be used with columns of type text unless the field is also saved as a keyword.

`MAX`

Synopsis:

MAX(field_name)

Input:

a numeric field. If this field contains only null values, the function returns null. Otherwise, the function ignores null values in this field.

Output: same type as the input

Description: Returns the maximum value across input values in the field field_name.

SELECT MAX(salary) AS max FROM emp;

      max
---------------
74999

SELECT MAX(ABS(salary / -12.0)) AS max FROM emp;

       max
-----------------
6249.916666666667

MAX on a field of type text or keyword is translated into LAST/LAST_VALUE and therefore, it cannot be used in HAVING clause.

`MIN`

Synopsis:

MIN(field_name)

Input:

a numeric field. If this field contains only null values, the function returns null. Otherwise, the function ignores null values in this field.

Output: same type as the input

Description: Returns the minimum value across input values in the field field_name.

SELECT MIN(salary) AS min FROM emp;

      min
---------------
25324

MIN on a field of type text or keyword is translated into FIRST/FIRST_VALUE and therefore, it cannot be used in HAVING clause.

`SUM`

Synopsis:

SUM(field_name)

Input:

a numeric field. If this field contains only null values, the function returns null. Otherwise, the function ignores null values in this field.

Output: bigint for integer input, double for floating points

Description: Returns the sum of input values in the field field_name.

SELECT SUM(salary) AS sum FROM emp;

      sum
---------------
4824855

SELECT ROUND(SUM(salary / 12.0), 1) AS sum FROM emp;

      sum
---------------
402071.3

Statistics

`KURTOSIS`

Synopsis:

KURTOSIS(field_name)

Input:

a numeric field. If this field contains only null values, the function returns null. Otherwise, the function ignores null values in this field.

Output: double numeric value

Description:

Quantify the shape of the distribution of input values in the field field_name.

SELECT MIN(salary) AS min, MAX(salary) AS max, KURTOSIS(salary) AS k FROM emp;

      min      |      max      |        k
---------------+---------------+------------------
25324          |74999          |2.0444718929142986

KURTOSIS cannot be used on top of scalar functions or operators but only directly on a field. So, for example, the following is not allowed and an error is returned:

 SELECT KURTOSIS(salary / 12.0), gender FROM emp GROUP BY gender

`MAD`

Synopsis:

MAD(field_name)

Input:

a numeric field. If this field contains only null values, the function returns null. Otherwise, the function ignores null values in this field.

Output: double numeric value

Description:

Measure the variability of the input values in the field field_name.

SELECT MIN(salary) AS min, MAX(salary) AS max, AVG(salary) AS avg, MAD(salary) AS mad FROM emp;

      min      |      max      |      avg      |      mad
---------------+---------------+---------------+---------------
25324          |74999          |48248.55       |10096.5

SELECT MIN(salary / 12.0) AS min, MAX(salary / 12.0) AS max, AVG(salary/ 12.0) AS avg, MAD(salary / 12.0) AS mad FROM emp;

       min        |       max       |      avg      |       mad
------------------+-----------------+---------------+-----------------
2110.3333333333335|6249.916666666667|4020.7125      |841.3750000000002

`PERCENTILE`

Synopsis:

PERCENTILE(
    field_name,         
    percentile[,        
    method[,            
    method_parameter]])

Input:

	a numeric field. If this field contains only `null` values, the function returns `null`. Otherwise, the function ignores `null` values in this field.
	a numeric expression (must be a constant and not based on a field). If `null`, the function returns `null`.
	optional string literal for the percentile algorithm. Possible values: `tdigest` or `hdr`. Defaults to `tdigest`.
	optional numeric literal that configures the percentile algorithm. Configures `compression` for `tdigest` or `number_of_significant_value_digits` for `hdr`. The default is the same as that of the backing algorithm.

Output: double numeric value

Description:

Returns the nth percentile (represented by numeric_exp parameter) of input values in the field field_name.

SELECT languages, PERCENTILE(salary, 95) AS "95th" FROM emp
       GROUP BY languages;

   languages   |      95th
---------------+-----------------
null           |74999.0
1              |72790.5
2              |71924.70000000001
3              |73638.25
4              |72115.59999999999
5              |61071.7

SELECT languages, PERCENTILE(salary / 12.0, 95) AS "95th" FROM emp
       GROUP BY languages;

   languages   |       95th
---------------+------------------
null           |6249.916666666667
1              |6065.875
2              |5993.725
3              |6136.520833333332
4              |6009.633333333332
5              |5089.3083333333325

SELECT
    languages,
    PERCENTILE(salary, 97.3, 'tdigest', 100.0) AS "97.3_TDigest",
    PERCENTILE(salary, 97.3, 'hdr', 3) AS "97.3_HDR"
FROM emp
GROUP BY languages;

   languages   | 97.3_TDigest  |   97.3_HDR
---------------+---------------+---------------
null           |74999.0        |74992.0
1              |73717.0        |73712.0
2              |73530.238      |69936.0
3              |74970.0        |74992.0
4              |74572.0        |74608.0
5              |66117.118      |56368.0

`PERCENTILE_RANK`

Synopsis:

PERCENTILE_RANK(
    field_name,         
    value[,             
    method[,            
    method_parameter]])

Input:

	a numeric field. If this field contains only `null` values, the function returns `null`. Otherwise, the function ignores `null` values in this field.
	a numeric expression (must be a constant and not based on a field). If `null`, the function returns `null`.
	optional string literal for the percentile algorithm. Possible values: `tdigest` or `hdr`. Defaults to `tdigest`.
	optional numeric literal that configures the percentile algorithm. Configures `compression` for `tdigest` or `number_of_significant_value_digits` for `hdr`. The default is the same as that of the backing algorithm.

Output: double numeric value

Description:

Returns the nth percentile rank (represented by numeric_exp parameter) of input values in the field field_name.

SELECT languages, PERCENTILE_RANK(salary, 65000) AS rank FROM emp GROUP BY languages;

   languages   |      rank
---------------+-----------------
null           |73.65766569962062
1              |73.7291625157734
2              |88.88005607010643
3              |79.43662623295829
4              |85.70446389643493
5              |100.0

SELECT languages, PERCENTILE_RANK(salary/12, 5000) AS rank FROM emp GROUP BY languages;

   languages   |       rank
---------------+------------------
null           |66.91240875912409
1              |66.70766707667076
2              |84.13266895048271
3              |61.052992625621684
4              |76.55646443990001
5              |94.00696864111498

SELECT
    languages,
    ROUND(PERCENTILE_RANK(salary, 65000, 'tdigest', 100.0), 2) AS "rank_TDigest",
    ROUND(PERCENTILE_RANK(salary, 65000, 'hdr', 3), 2) AS "rank_HDR"
FROM emp
GROUP BY languages;

   languages   | rank_TDigest  |   rank_HDR
---------------+---------------+---------------
null           |73.66          |80.0
1              |73.73          |73.33
2              |88.88          |89.47
3              |79.44          |76.47
4              |85.7           |83.33
5              |100.0          |95.24

`SKEWNESS`

Synopsis:

SKEWNESS(field_name)

Input:

a numeric field. If this field contains only null values, the function returns null. Otherwise, the function ignores null values in this field.

Output: double numeric value

Description:

Quantify the asymmetric distribution of input values in the field field_name.

SELECT MIN(salary) AS min, MAX(salary) AS max, SKEWNESS(salary) AS s FROM emp;

      min      |      max      |        s
---------------+---------------+------------------
25324          |74999          |0.2707722118423227

SKEWNESS cannot be used on top of scalar functions but only directly on a field. So, for example, the following is not allowed and an error is returned:

 SELECT SKEWNESS(ROUND(salary / 12.0, 2), gender FROM emp GROUP BY gender

`STDDEV_POP`

Synopsis:

STDDEV_POP(field_name)

Input:

a numeric field. If this field contains only null values, the function returns null. Otherwise, the function ignores null values in this field.

Output: double numeric value

Description:

Returns the population standard deviation of input values in the field field_name.

SELECT MIN(salary) AS min, MAX(salary) AS max, STDDEV_POP(salary) AS stddev FROM emp;

      min      |      max      |      stddev
---------------+---------------+------------------
25324          |74999          |13765.125502787832

SELECT MIN(salary / 12.0) AS min, MAX(salary / 12.0) AS max, STDDEV_POP(salary / 12.0) AS stddev FROM emp;

       min        |       max       |     stddev
------------------+-----------------+-----------------
2110.3333333333335|6249.916666666667|1147.093791898986

`STDDEV_SAMP`

Synopsis:

STDDEV_SAMP(field_name)

Input:

a numeric field. If this field contains only null values, the function returns null. Otherwise, the function ignores null values in this field.

Output: double numeric value

Description:

Returns the sample standard deviation of input values in the field field_name.

SELECT MIN(salary) AS min, MAX(salary) AS max, STDDEV_SAMP(salary) AS stddev FROM emp;

      min      |      max      |      stddev
---------------+---------------+------------------
25324          |74999          |13834.471662090747

SELECT MIN(salary / 12.0) AS min, MAX(salary / 12.0) AS max, STDDEV_SAMP(salary / 12.0) AS stddev FROM emp;

       min        |       max       |     stddev
------------------+-----------------+-----------------
2110.3333333333335|6249.916666666667|1152.872638507562

`SUM_OF_SQUARES`

Synopsis:

SUM_OF_SQUARES(field_name)

Input:

a numeric field. If this field contains only null values, the function returns null. Otherwise, the function ignores null values in this field.

Output: double numeric value

Description:

Returns the sum of squares of input values in the field field_name.

SELECT MIN(salary) AS min, MAX(salary) AS max, SUM_OF_SQUARES(salary) AS sumsq
       FROM emp;

      min      |      max      |     sumsq
---------------+---------------+----------------
25324          |74999          |2.51740125721E11

SELECT MIN(salary / 24.0) AS min, MAX(salary / 24.0) AS max, SUM_OF_SQUARES(salary / 24.0) AS sumsq FROM emp;

       min        |       max        |       sumsq
------------------+------------------+-------------------
1055.1666666666667|3124.9583333333335|4.370488293767361E8

`VAR_POP`

Synopsis:

VAR_POP(field_name)

Input:

a numeric field. If this field contains only null values, the function returns null. Otherwise, the function ignores null values in this field.

Output: double numeric value

Description:

Returns the population variance of input values in the field field_name.

SELECT MIN(salary) AS min, MAX(salary) AS max, VAR_POP(salary) AS varpop FROM emp;

      min      |      max      |     varpop
---------------+---------------+----------------
25324          |74999          |1.894786801075E8

SELECT MIN(salary / 24.0) AS min, MAX(salary / 24.0) AS max, VAR_POP(salary / 24.0) AS varpop FROM emp;

       min        |       max        |      varpop
------------------+------------------+------------------
1055.1666666666667|3124.9583333333335|328956.04185329855

`VAR_SAMP`

Synopsis:

VAR_SAMP(field_name)

Input:

a numeric field. If this field contains only null values, the function returns null. Otherwise, the function ignores null values in this field.

Output: double numeric value

Description:

Returns the sample variance of input values in the field field_name.

SELECT MIN(salary) AS min, MAX(salary) AS max, VAR_SAMP(salary) AS varsamp FROM emp;

      min      |      max      |     varsamp
---------------+---------------+----------------
25324          |74999          |1.913926061691E8

SELECT MIN(salary / 24.0) AS min, MAX(salary / 24.0) AS max, VAR_SAMP(salary / 24.0) AS varsamp FROM emp;

       min        |       max        |     varsamp
------------------+------------------+----------------
1055.1666666666667|3124.9583333333335|332278.830154847

© 2023-2025 Elasticsearch
As of September 2024, Elasticsearch is available under a choice of three licenses: the Server Side Public License (SSPL), the Elastic License, or the AGPLv3 (OSI approved).
Elasticsearch and the Elasticsearch logo are trademarks of Elasticsearch B.V., registered in the U.S. and in other countries.
https://www.elastic.co/guide/en/elasticsearch/reference/7.17/sql-functions-aggs.html