IMPORTANT: No additional bug fixes or documentation updates will be released for this version. For the latest information, see the current release documentation.

« Math Operators Date and Time Functions »

› › ›

Aggregate Functions

edit

IMPORTANT: This documentation is no longer updated. Refer to Elastic's version policy and the latest documentation.

Aggregate Functions

edit

This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.

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

edit

`AVG`

edit

Input: Numeric, Output: double

Average (arithmetic mean) of input values.

SELECT AVG(salary) AS avg FROM emp;

      avg:d
---------------
48248

`COUNT`

edit

Input: Any, Output: bigint

Total number (count) of input values.

SELECT COUNT(*) AS count FROM emp;

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

`COUNT(DISTINCT)`

edit

Input: Any, Output: bigint

Total number of distinct values in input values.

SELECT COUNT(DISTINCT hire_date) AS hires FROM emp;

     hires
---------------
99

`MAX`

edit

Input: Numeric, Output: Same as input

Maximum value across input values.

SELECT MAX(salary) AS max FROM emp;

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

`MIN`

edit

Input: Numeric, Output: Same as input

Minimum value across input values.

SELECT MIN(salary) AS min FROM emp;

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

`SUM`

edit

Input: Numeric, Output: bigint for integer input, double for floating points

Sum of input values.

SELECT SUM(salary) AS sum FROM emp;

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

Statistics

edit

`KURTOSIS`

edit

Input: Numeric, Output: double

Quantify the shape of the distribution of input values.

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

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

`PERCENTILE`

edit

Input: Numeric, Output: double

The nth percentile of input values.

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

`PERCENTILE_RANK`

edit

Input: Numeric, Output: double

The percentile rank of input values of input values.

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

`SKEWNESS`

edit

Input: Numeric, Output: double

Quantify the asymmetric distribution of input values.

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

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

`STDDEV_POP`

edit

Input: Numeric, Output: double

Population standard deviation of input values.

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

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

`SUM_OF_SQUARES`

edit

Input: Numeric, Output: double

Sum of squares of input values.

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

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

`VAR_POP`

edit

Input: Numeric, Output: double

Population variance of input values.

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

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

« Math Operators Date and Time Functions »