Overview

An aggregate function performs a calculation on a set of values and returns a single scalar value.

Vertical aggregation is supported which takes a set of values from different rows and aggregates into a single value. Horizontal aggregation which takes a set of values from a group list value and aggregates into a single value is not yet supported.

DISTINCT

Functions avg(), count(), max(), min(), sum(), collect_list(), stddev_samp(), and stddev_pop() support the use of the set quantifier DISTINCT to deduplicate values before aggregation.

Null Values

Rows containing null values are ignored by all aggregate functions, except count(*).

Example Graph

The following examples run against this graph:

avg()

Computes the average of a set of values.

MATCH (n)
RETURN avg(n.score)

Result:

FOR item IN [2, "a", 3, "1.2", null]
RETURN avg(item)

Result:

collect_list()

Collects a set of values into a list.

MATCH (n)
RETURN collect_list(n.title)

Result:

count()

Returns the number of rows in the input.

MATCH (n)
RETURN count(n)

Result:

count(*)

count(*) returns the number of rows in the current working table.

Comparing the following two queries, the null values are only considered when using count(*):

FOR item IN [1, "a", "2", "b3", null]
RETURN count(item)

Result:

FOR item IN [1, "a", "2", "b3", null]
RETURN count(*)

Result:

count(DISTINCT)

You can include the set quantifier DISTINCT in count() to return the number of distinct rows in the input.

FOR item IN [1, 1, "a", "2", "b3"]
RETURN count(DISTINCT item)

Result:

max()

Returns the maximum value in a set of values.

MATCH (n)
RETURN max(n.score)

Result:

FOR item IN [1, "a", "2.1", "b3"]
RETURN max(item)

Result:

min()

Returns the minimum value in a set of values.

MATCH (n)
RETURN min(n.score)

Result:

FOR item IN [3, "a", "0.2", "b2"]
RETURN min(item)

Result:

percentile_cont()

Computes the continuous percentile value over a set of values.

percentile_cont() is computed using the following steps:

• Sort the values in ascending order.
• Compute the percentile position as p = percentile × (n − 1) + 1, where n is the number of non-null values.
• Determine the percentile value using linear interpolation:
  • If p is an integer, the corresponding value at that position is the percentile value.
  • If p is a decimal between two integers p1 and p2 (p1 < p < p2), interpolate between the value v1 at position p1 and the value v2 at position p2 to compute the percentile value as v1 + (p - p1) × (v2 - v1).

FOR item IN [3, 9, 4, 7, 6]
RETURN percentile_cont(item, 0.4)

Result:

FOR item IN [3, 9, 4, 7, 6]
RETURN percentile_cont(item, 0.5)

Result:

percentile_disc()

Computes the discrete percentile value over a set of values.

percentile_disc() is computed using the following steps:

• Sort the values in ascending order.
• Compute the percentile position as p = percentile × (n − 1) + 1, where n is the number of non-null values.
• Determine the percentile value:
  • If p is an integer, the value at that exact position is selected as the percentile value.
  • If p is not an integer, remove the decimal portion of p and gain p', the value at the position p' is selected as the percentile value.

FOR item IN [3, 9, 4, 7, 6]
RETURN percentile_disc(item, 0.4)

Result:

FOR item IN [3, 9, 4, 7, 6]
RETURN percentile_disc(item, 0.5)

Result:

stddev_pop()

Computes the population standard deviation of a set of values.

MATCH (n)
RETURN stddev_pop(n.score)

Result:

stddev_samp()

Computes the sample standard deviation of a set of values.

MATCH (n)
RETURN stddev_samp(n.score)

Result:

sum()

Computes the sum of a set of values.

MATCH (n)
RETURN sum(n.score)

Result:

FOR item IN [3, "a", "1.2", "b2"]
RETURN sum(item)

Result: