ES|QL COUNT function

Returns the total number of input values.

field

Expression that outputs values to be counted. If omitted, equivalent to COUNT(*) (the number of rows).

bucket

Range of histogram values to count.

Returns the total number (count) of input values.

FROM employees
| STATS COUNT(height)

To count the number of rows, use COUNT() or COUNT(*)

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

The expression can use inline functions. This example splits a string into multiple values using the SPLIT function and counts the values.

ROW words="foo;bar;baz;qux;quux;foo"
| STATS word_count = COUNT(SPLIT(words, ";"))

To count the number of times an expression returns TRUE use a WHERE command to remove rows that shouldn’t be included.

ROW n=1
| WHERE n < 0
| STATS COUNT(n)

To count the number of times multiple expressions return TRUE use a WHERE inside the STATS.

FROM employees
| STATS
    gte20 = COUNT(*) WHERE height >= 2,
    lte18 = COUNT(*) WHERE height <= 1.8

COUNTing a multivalued field returns the number of values. COUNTing NULL returns 0. COUNTing true returns 1. COUNTing false returns 1.

ROW mv = [1, 2], n = NULL, t = TRUE, f = FALSE
| STATS COUNT(mv), COUNT(n), COUNT(t), COUNT(f)

You may see a pattern like COUNT(<expression> OR NULL). This has the same meaning as COUNT() WHERE <expression>. This relies on COUNT(NULL) to return 0 and builds on the three-valued logic (3VL): TRUE OR NULL is TRUE, but FALSE OR NULL is NULL. Prefer the COUNT() WHERE <expression> pattern.

ROW n=1
| STATS COUNT(n > 0 OR NULL), COUNT(n < 0 OR NULL)

COUNT can also operate on exponential_histogram fields, returning the total number of values which were used to construct the histograms.

TS exp_histo_sample
| WHERE instance == "instance-0"
| STATS cnt = COUNT(responseTime)

COUNT can also operate on tdigest and casted histogram fields, returning the total number of values which were used to construct the digests.

TS histogram_timeseries_index
| WHERE instance == "instance-0"
| STATS cnt = COUNT(responseTime::tdigest)

To count only the histogram values that fall into a range, pass a double_range (for example from TO_RANGE) as the second argument. Combine this with BUCKET on the same histogram field to create a histogram of the recorded values:

TS exp_histo_sample
| WHERE instance == "instance-0"
| STATS count = COUNT(responseTime, bucket) BY bucket = BUCKET(responseTime, 1)
| SORT RANGE_MIN(bucket)

The same works for tdigest and casted histogram fields:

TS histogram_timeseries_index
| WHERE instance == "instance-0"
| STATS count = COUNT(responseTime::tdigest, bucket) BY bucket = BUCKET(responseTime::tdigest, 1)
| SORT RANGE_MIN(bucket)

A tdigest does not track which ranges between its centroids are empty, so BUCKET returns every bucket between the smallest and the largest centroid of a digest, and some of them may end up with a count of 0.