elasticsearch/docs/reference/sql/functions/aggs.asciidoc

[role="xpack"]
[testenv="basic"]
[[sql-functions-aggs]]
=== Aggregate Functions

Functions for computing a _single_ result from a set of input values.
{es-sql} supports aggregate functions only alongside <<sql-syntax-group-by,grouping>> (implicit or explicit).

[[sql-functions-aggs-general]]
[discrete]
=== General Purpose

[[sql-functions-aggs-avg]]
==== `AVG`

.Synopsis:
[source, sql]
--------------------------------------------------
AVG(numeric_field) <1>
--------------------------------------------------

*Input*:

<1> 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 {wikipedia}/Arithmetic_mean[Average] (arithmetic mean) of input values.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggAvg]
--------------------------------------------------

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggAvgScalars]
--------------------------------------------------

[[sql-functions-aggs-count]]
==== `COUNT`

.Synopsis:
[source, sql]
--------------------------------------------------
COUNT(expression) <1>
--------------------------------------------------

*Input*:

<1> 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.


["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggCountStar]
--------------------------------------------------


[[sql-functions-aggs-count-all]]
==== `COUNT(ALL)`

.Synopsis:
[source, sql]
--------------------------------------------------
COUNT(ALL field_name) <1>
--------------------------------------------------

*Input*:

<1> 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.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggCountAll]
--------------------------------------------------

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggCountAllScalars]
--------------------------------------------------

[[sql-functions-aggs-count-distinct]]
==== `COUNT(DISTINCT)`

.Synopsis:
[source, sql]
--------------------------------------------------
COUNT(DISTINCT field_name) <1>
--------------------------------------------------

*Input*:

<1> 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.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggCountDistinct]
--------------------------------------------------

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggCountDistinctScalars]
--------------------------------------------------

[[sql-functions-aggs-first]]
==== `FIRST/FIRST_VALUE`

.Synopsis:
[source, sql]
----------------------------------------------
FIRST(
    field_name               <1>
    [, ordering_field_name]) <2>
----------------------------------------------

*Input*:

<1> target field for the aggregation
<2> 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.:

[cols="<,<"]
|===
s| a    | b

 | 100  | 1
 | 200  | 1
 | 1    | 2
 | 2    | 2
 | 10   | null
 | 20   | null
 | null | null
|===

[source, sql]
----------------------
SELECT FIRST(a) FROM t
----------------------

will result in:
[cols="<"]
|===
s| FIRST(a)
 | 1
|===

and

[source, sql]
-------------------------
SELECT FIRST(a, b) FROM t
-------------------------

will result in:
[cols="<"]
|===
s| FIRST(a, b)
 | 100
|===


["source","sql",subs="attributes,macros"]
-----------------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[firstWithOneArg]
-----------------------------------------------------------

["source","sql",subs="attributes,macros"]
--------------------------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[firstWithOneArgAndGroupBy]
--------------------------------------------------------------------

["source","sql",subs="attributes,macros"]
-----------------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[firstWithTwoArgs]
-----------------------------------------------------------

["source","sql",subs="attributes,macros"]
---------------------------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[firstWithTwoArgsAndGroupBy]
---------------------------------------------------------------------

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

["source","sql",subs="attributes,macros"]
--------------------------------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[firstValueWithTwoArgsAndGroupBy]
--------------------------------------------------------------------------

["source","sql",subs="attributes,macros"]
--------------------------------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[firstValueWithTwoArgsAndGroupByScalars]
--------------------------------------------------------------------------

[NOTE]
`FIRST` cannot be used in a HAVING clause.
[NOTE]
`FIRST` cannot be used with columns of type <<text, `text`>> unless
the field is also <<before-enabling-fielddata,saved as a keyword>>.

[[sql-functions-aggs-last]]
==== `LAST/LAST_VALUE`

.Synopsis:
[source, sql]
--------------------------------------------------
LAST(
    field_name               <1>
    [, ordering_field_name]) <2>
--------------------------------------------------

*Input*:

<1> target field for the aggregation
<2> optional field used for ordering

*Output*: same type as the input

*Description*: It's the inverse of <<sql-functions-aggs-first>>. 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.:

[cols="<,<"]
|===
s| a    | b

 | 10   | 1
 | 20   | 1
 | 1    | 2
 | 2    | 2
 | 100  | null
 | 200  | null
 | null | null
|===

[source, sql]
------------------------
SELECT LAST(a) FROM t
------------------------

will result in:
[cols="<"]
|===
s| LAST(a)
 | 200
|===

and

[source, sql]
------------------------
SELECT LAST(a, b) FROM t
------------------------

will result in:
[cols="<"]
|===
s| LAST(a, b)
 | 2
|===


["source","sql",subs="attributes,macros"]
-----------------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[lastWithOneArg]
-----------------------------------------------------------

["source","sql",subs="attributes,macros"]
-------------------------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[lastWithOneArgAndGroupBy]
-------------------------------------------------------------------

["source","sql",subs="attributes,macros"]
-----------------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[lastWithTwoArgs]
-----------------------------------------------------------

["source","sql",subs="attributes,macros"]
--------------------------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[lastWithTwoArgsAndGroupBy]
--------------------------------------------------------------------

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

["source","sql",subs="attributes,macros"]
-------------------------------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[lastValueWithTwoArgsAndGroupBy]
-------------------------------------------------------------------------

["source","sql",subs="attributes,macros"]
-------------------------------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[lastValueWithTwoArgsAndGroupByScalars]
-------------------------------------------------------------------------

[NOTE]
`LAST` cannot be used in `HAVING` clause.
[NOTE]
`LAST` cannot be used with columns of type <<text, `text`>> unless
the field is also <<before-enabling-fielddata,`saved as a keyword`>>.

[[sql-functions-aggs-max]]
==== `MAX`

.Synopsis:
[source, sql]
--------------------------------------------------
MAX(field_name) <1>
--------------------------------------------------

*Input*:

<1> 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`.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggMax]
--------------------------------------------------

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggMaxScalars]
--------------------------------------------------

[NOTE]
`MAX` on a field of type <<text, `text`>> or <<keyword, `keyword`>> is translated into
<<sql-functions-aggs-last>> and therefore, it cannot be used in `HAVING` clause.

[[sql-functions-aggs-min]]
==== `MIN`

.Synopsis:
[source, sql]
--------------------------------------------------
MIN(field_name) <1>
--------------------------------------------------

*Input*:

<1> 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`.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggMin]
--------------------------------------------------

[NOTE]
`MIN` on a field of type <<text, `text`>> or <<keyword, `keyword`>> is translated into
<<sql-functions-aggs-first>> and therefore, it cannot be used in `HAVING` clause.

[[sql-functions-aggs-sum]]
==== `SUM`

.Synopsis:
[source, sql]
--------------------------------------------------
SUM(field_name) <1>
--------------------------------------------------

*Input*:

<1> 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`.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggSum]
--------------------------------------------------

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggSumScalars]
--------------------------------------------------

[[sql-functions-aggs-statistics]]
[discrete]
=== Statistics

[[sql-functions-aggs-kurtosis]]
==== `KURTOSIS`

.Synopsis:
[source, sql]
--------------------------------------------------
KURTOSIS(field_name) <1>
--------------------------------------------------

*Input*:

<1> 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*:

{wikipedia}/Kurtosis[Quantify] the shape of the distribution of input values in the field `field_name`.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggKurtosis]
--------------------------------------------------

[NOTE]
====
`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:
[source, sql]
---------------------------------------
 SELECT KURTOSIS(salary / 12.0), gender FROM emp GROUP BY gender
---------------------------------------
====

[[sql-functions-aggs-mad]]
==== `MAD`

.Synopsis:
[source, sql]
--------------------------------------------------
MAD(field_name) <1>
--------------------------------------------------

*Input*:

<1> 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*:

{wikipedia}/Median_absolute_deviation[Measure] the variability of the input values in the field `field_name`.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggMad]
--------------------------------------------------

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggMadScalars]
--------------------------------------------------

[[sql-functions-aggs-percentile]]
==== `PERCENTILE`

.Synopsis:
[source, sql]
--------------------------------------------------
PERCENTILE(
    field_name,         <1>
    percentile[,        <2>
    method[,            <3>
    method_parameter]]) <4>
--------------------------------------------------

*Input*:

<1> a numeric field. If this field contains only `null` values, the function
returns `null`. Otherwise, the function ignores `null` values in this field.
<2> a numeric expression (must be a constant and not based on a field). If
`null`, the function returns `null`.
<3> optional string literal for the <<search-aggregations-metrics-percentile-aggregation-approximation,percentile algorithm>>. Possible values: `tdigest` or `hdr`. Defaults to `tdigest`.
<4> optional numeric literal that configures the <<search-aggregations-metrics-percentile-aggregation-approximation,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 {wikipedia}/Percentile[percentile] (represented by `numeric_exp` parameter)
of input values in the field `field_name`.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggPercentile]
--------------------------------------------------

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggPercentileScalars]
--------------------------------------------------

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggPercentileWithPercentileConfig]
--------------------------------------------------

[[sql-functions-aggs-percentile-rank]]
==== `PERCENTILE_RANK`

.Synopsis:
[source, sql]
--------------------------------------------------
PERCENTILE_RANK(
    field_name,         <1>
    value[,             <2>
    method[,            <3>
    method_parameter]]) <4>
--------------------------------------------------

*Input*:

<1> a numeric field. If this field contains only `null` values, the function
returns `null`. Otherwise, the function ignores `null` values in this field.
<2> a numeric expression (must be a constant and not based on a field). If
`null`, the function returns `null`.
<3> optional string literal for the <<search-aggregations-metrics-percentile-aggregation-approximation,percentile algorithm>>. Possible values: `tdigest` or `hdr`. Defaults to `tdigest`.
<4> optional numeric literal that configures the <<search-aggregations-metrics-percentile-aggregation-approximation,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 {wikipedia}/Percentile_rank[percentile rank] (represented by `numeric_exp` parameter)
of input values in the field `field_name`.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggPercentileRank]
--------------------------------------------------

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggPercentileRankScalars]
--------------------------------------------------

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggPercentileRankWithPercentileConfig]
--------------------------------------------------

[[sql-functions-aggs-skewness]]
==== `SKEWNESS`

.Synopsis:
[source, sql]
--------------------------------------------------
SKEWNESS(field_name) <1>
--------------------------------------------------

*Input*:

<1> 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*:

{wikipedia}/Skewness[Quantify] the asymmetric distribution of input values in the field `field_name`.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggSkewness]
--------------------------------------------------

[NOTE]
====
`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:
[source, sql]
---------------------------------------
 SELECT SKEWNESS(ROUND(salary / 12.0, 2), gender FROM emp GROUP BY gender
---------------------------------------
====

[[sql-functions-aggs-stddev-pop]]
==== `STDDEV_POP`

.Synopsis:
[source, sql]
--------------------------------------------------
STDDEV_POP(field_name) <1>
--------------------------------------------------

*Input*:

<1> 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 {wikipedia}/Standard_deviations[population standard deviation] of input values in the field `field_name`.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggStddevPop]
--------------------------------------------------

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggStddevPopScalars]
--------------------------------------------------

[[sql-functions-aggs-stddev-samp]]
==== `STDDEV_SAMP`

.Synopsis:
[source, sql]
--------------------------------------------------
STDDEV_SAMP(field_name) <1>
--------------------------------------------------

*Input*:

<1> 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 {wikipedia}/Standard_deviations[sample standard deviation] of input values in the field `field_name`.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggStddevSamp]
--------------------------------------------------

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggStddevSampScalars]
--------------------------------------------------

[[sql-functions-aggs-sum-squares]]
==== `SUM_OF_SQUARES`

.Synopsis:
[source, sql]
--------------------------------------------------
SUM_OF_SQUARES(field_name) <1>
--------------------------------------------------

*Input*:

<1> 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`.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggSumOfSquares]
--------------------------------------------------

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggSumOfSquaresScalars]
--------------------------------------------------

[[sql-functions-aggs-var-pop]]
==== `VAR_POP`

.Synopsis:
[source, sql]
--------------------------------------------------
VAR_POP(field_name) <1>
--------------------------------------------------

*Input*:

<1> 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 {wikipedia}/Variance[population variance] of input values in the field `field_name`.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggVarPop]
--------------------------------------------------


["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggVarPopScalars]
--------------------------------------------------

[[sql-functions-aggs-var-samp]]
==== `VAR_SAMP`

.Synopsis:
[source, sql]
--------------------------------------------------
VAR_SAMP(field_name) <1>
--------------------------------------------------

*Input*:

<1> 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 {wikipedia}/Variance[sample variance] of input values in the field `field_name`.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggVarSamp]
--------------------------------------------------


["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[aggVarSampScalars]
--------------------------------------------------