The Open Distro project is archived. Open Distro development has moved to OpenSearch. The Open Distro plugins will continue to work with legacy versions of Elasticsearch OSS, but we recommend upgrading to OpenSearch to take advantage of the latest features and improvements.

Aggregation functions

Aggregate functions use the GROUP BY clause to group sets of values into subsets.

Group By

Use the GROUP BY clause as an identifier, ordinal, or expression.

Identifier

SELECT gender, sum(age) FROM accounts GROUP BY gender;

Ordinal

SELECT gender, sum(age) FROM accounts GROUP BY 1;

Expression

SELECT abs(account_number), sum(age) FROM accounts GROUP BY abs(account_number);

Aggregation

Use aggregations as a select, expression, or an argument of an expression.

Select

SELECT gender, sum(age) FROM accounts GROUP BY gender;

Argument

SELECT gender, sum(age) * 2 as sum2 FROM accounts GROUP BY gender;

Expression

SELECT gender, sum(age * 2) as sum2 FROM accounts GROUP BY gender;

COUNT

Use the COUNT function to accept arguments such as a * or a literal like 1. The meaning of these different forms are as follows:

• COUNT(field) - Only counts if given a field (or expression) is not null or missing in the input rows.
• COUNT(*) - Counts the number of all its input rows.
• COUNT(1) (same as COUNT(*)) - Counts any non-null literal.

Having

Use the HAVING clause to filter out aggregated values.

HAVING with GROUP BY

You can use aggregate expressions or its aliases defined in a SELECT clause in a HAVING condition.

We recommend using a non-aggregate expression in the WHERE clause although you can do this in a HAVING clause.

The aggregations in a HAVING clause are not necessarily the same as that in a select list. As an extension to the SQL standard, you’re not restricted to using identifiers only in the GROUP BY list. For example:

SELECT gender, sum(age)
FROM accounts
GROUP BY gender
HAVING sum(age) > 100;

Here’s another example for using an alias in a HAVING condition.

SELECT gender, sum(age) AS s
FROM accounts
GROUP BY gender
HAVING s > 100;

If an identifier is ambiguous, for example, present both as a select alias and as an index field (preference is alias). In this case, the identifier is replaced with an expression aliased in the SELECT clause:

HAVING without GROUP BY

You can use a HAVING clause without the GROUP BY clause. This is useful because aggregations are not supported in a WHERE clause:

SELECT 'Total of age > 100'
FROM accounts
HAVING sum(age) > 100;