Skip to content
Developer Edition runs on one host and does not require a product license file. Need multi-host deployment or performance testing at scale? Explore Enterprise Edition

Aggregate functions

Aggregate functions in GQL perform calculations over a group of values and return a single value. To view the supported data types of each aggregate function, use SHOW FUNCTION.

The keyword DISTINCT can be used with aggregate functions to eliminate duplicate values before calculation. For instance, sum(DISTINCT x) computes the sum of all unique values of x. For more information, see Aggregate value expression.

Note

The aggregator value variables (such as SumAgg<T>, AvgAgg<T>, MaxAgg<T>, MinAgg<T>, ListAgg<T>, SetAgg<T>, and MapAgg<K, V>) support more explicit type variants, including INT8, INT16, INT32, INT64, FLOAT, and DOUBLE. For more information, see Aggregator value variables.

avg()

Returns the average value of a group of values.

Null behavior: ignore

Function signature

avg(num: Numeric) -> DOUBLE | DECIMAL

If num is of data type DECIMAL, the return value is of data type DECIMAL; otherwise, the return value is of data type DOUBLE.

Examples

Example 1:

FOR i IN LIST [1,2,3,4,5,5] RETURN avg(i) GROUP BY ()

Return:

+--------------------+
| avg(i)             |
+--------------------+
| 3.3333333333333335 |
+--------------------+

Example 2:

FOR i IN LIST [1,2,3,4,5,5] RETURN avg(DISTINCT i) GROUP BY ()

Return:

+-----------------+
| avg(DISTINCT i) |
+-----------------+
| 3.0             |
+-----------------+

Example 3:

'''
USE movie 
MATCH (v1)-[e:Watch]->(v2 {id:199}) 
RETURN avg(e.rate) AS rate, v2.name AS movie GROUP BY movie
'''

Return:

+-------------------+--------------------------+
| rate              | movie                    |
+-------------------+--------------------------+
| 3.879120879120879 | Star Trek: First Contact |
+-------------------+--------------------------+

collect() and collect_list()

The collect() and collect_list() functions return an aggregated list from a group of values.

NULL behavior: ignore

Function signature

collect(n: E) -> LIST

or

collect_list(n: E) -> LIST

E represents any data type.

Examples

Example 1:

FOR i IN LIST [1,2,3,4,5,5] RETURN collect(i) GROUP BY ()

Return:

+---------------+
| collect(i)    |
+---------------+
| [1,2,3,4,5,5] |
+---------------+

Example 2:

FOR i IN LIST [LIST[1,2],LIST[2,3,4]] RETURN collect(i) GROUP BY ()

Return:

+-----------------+
| collect(i)      |
+-----------------+
| [[1,2],[2,3,4]] |
+-----------------+

Example 3:

FOR i IN LIST [1,2,3,4,5,5] RETURN collect_list(DISTINCT i) GROUP BY ()

Return:

+--------------------------+
| collect_list(DISTINCT i) |
+--------------------------+
| [5,4,3,2,1]              |
+--------------------------+

Example 4:

'''
USE movie 
MATCH (v1)-[e:Watch]->(v2 {id:1}) 
ORDER BY e.rate ASC 
RETURN collect(e.rate) AS rate_collect, v2.name AS movie GROUP BY movie
'''

Return:

+-----------------------------------------------------------------------------------+--------------+
| rate_collect                                                                      | movie        |
+-----------------------------------------------------------------------------------+--------------+
| [2.0,2.5,3.5,3.5,3.5,3.5,3.5,3.5,4.0,4.0,4.0,4.0,4.5,4.5,4.5,4.5,4.5,4.5,4.5,5.0] | Cowboy Bebop |
+-----------------------------------------------------------------------------------+--------------+

count()

Returns the number of values in a group.

  • If count() is used with * as an argument, it returns the number of all values, including null values.
  • If count() is used with a single argument, it returns the number of all non-null values.

Function signature

count(n: E) -> INT64

E represents any data type.

Examples

Example 1:

FOR i IN LIST [1,2,3,4,5,5] RETURN count(i) GROUP BY ()

Return:

+----------+
| count(i) |
+----------+
| 6        |
+----------+

Example 2:

FOR i IN LIST [1,2,3,4,5,5] RETURN count(DISTINCT i) GROUP BY ()

Return:

+-------------------+
| count(DISTINCT i) |
+-------------------+
| 5                 |
+-------------------+

Example 3:

FOR i IN LIST [1,2,3,4,5,5,null] RETURN count(*) GROUP BY ()

Return:

+----------+
| count(*) |
+----------+
| 7        |
+----------+

Example 4:

'''
USE movie 
MATCH (v1)-[e:Watch]->(v2 {id:199}) 
RETURN count(e.rate) AS rate_count, v2.name AS movie GROUP BY movie
'''

Return:

+------------+--------------------------+
| rate_count | movie                    |
+------------+--------------------------+
| 91         | Star Trek: First Contact |
+------------+--------------------------+

max()

Returns the maximum value in a group.

NULL behavior: ignore

Function signature

max(n: E) -> E

E represents one of the following data types: numeric type, string type and temporal type. The return value is of the same data type as the input values. For more information about the comparison rules of different data types, see comparison operators.

Note

For data of temporal type DURATION, all values compared by the aggregate functions max() and min() must belong to the same unit group. For example, you cannot compare a duration in the year and month-based duration unit group (for example, P1Y2M) with a duration in the day and time-based duration unit group (for example, PT10H).

Examples

Example 1:

FOR i IN LIST [1,2,3,4,5,5] RETURN max(i) GROUP BY ()

Return:

+--------+
| max(i) |
+--------+
| 5      |
+--------+

Example 2:

FOR i IN LIST [1,2,3,4,5,5] RETURN max(DISTINCT i) GROUP BY ()

Return:

+-----------------+
| max(DISTINCT i) |
+-----------------+
| 5               |
+-----------------+

Example 3:

'''
USE movie 
MATCH (v1)-[e:Watch]->(v2 {id:199}) 
RETURN max(e.rate) AS rate_max, v2.name AS movie GROUP BY movie
'''

Return:

+----------+--------------------------+
| rate_max | movie                    |
+----------+--------------------------+
| 5.0      | Star Trek: First Contact |
+----------+--------------------------+

Example 4:

FOR i IN LIST ["apple", "banana", "apricot"] RETURN max(i) AS a GROUP BY ()

Return:

+--------+
| a      |
+--------+
| banana |
+--------+

Example 5:

FOR i IN LIST [duration("P1Y2M"), duration("P1Y1M"), duration("P2Y")] RETURN max(i) AS a GROUP BY ()

Return:

+-----+
| a   |
+-----+
| P2Y |
+-----+

min()

Returns the minimum value in a group.

NULL behavior: ignore

Function signature

min(n: E) -> E

E represents one of the following data types: numeric type, string type and temporal type. The return value is of the same data type as the input values. For more information about the comparison rules of different data types, see comparison operators.

Note

For data of temporal type DURATION, all values compared by the aggregate functions max() and min() must belong to the same unit group. For example, you cannot compare a duration in the year and month-based duration unit group (for example, P1Y2M) with a duration in the day and time-based duration unit group (for example, PT10H).

Examples

Example 1:

FOR i IN LIST [1,2,3,4,5,5] RETURN min(i) GROUP BY ()

Return:

+--------+
| min(i) |
+--------+
| 1      |
+--------+

Example 2:

FOR i IN LIST [1,2,3,4,5,5] RETURN min(DISTINCT i) GROUP BY ()

Return:

+-----------------+
| min(DISTINCT i) |
+-----------------+
| 1               |
+-----------------+

Example 3:

'''
USE movie 
MATCH (v1)-[e:Watch]->(v2 {id:199}) 
RETURN min(e.rate) AS rate_min, v2.name AS movie GROUP BY movie
'''

Return:

+----------+--------------------------+
| rate_min | movie                    |
+----------+--------------------------+
| 0.5      | Star Trek: First Contact |
+----------+--------------------------+

Example 4:

FOR i IN LIST ["user", "username", "usergroup"] RETURN min(i) AS a GROUP BY ()

Return:

+----------+
| a        |
+----------+
| user     |
+----------+

Example 5:

FOR i IN LIST [duration("P1Y6M"), duration("P1Y9M"), duration("P1Y2M")] RETURN min(i) AS a GROUP BY ()

Return:

+------------+
| a          |
+------------+
| P1Y2M      |
+------------+

sum()

Returns the sum of a group of values.

NULL behavior: ignore

Function signature

sum(num: Numeric) -> UINT64 | INT64 | DOUBLE | DECIMAL
  • If num is a signed integer, the return value is of data type INT64.
  • If num is an unsigned integer, the return value is of data type UINT64.
  • If num is a floating-point number, the return value is of data type DOUBLE.
  • If num is an arbitrary-precision number, the return value is of data type DECIMAL.

Examples

Example 1:

FOR i IN LIST [1,2,3,4,5,5] RETURN sum(i) GROUP BY ()

Return:

+--------+
| sum(i) |
+--------+
| 20     |
+--------+

Example 2:

FOR i IN LIST [1,2,3,4,5,5] RETURN sum(DISTINCT i) GROUP BY ()

Return:

+-----------------+
| sum(DISTINCT i) |
+-----------------+
| 15              |
+-----------------+

Example 3:

'''
USE movie 
MATCH (v1)-[e:Watch]->(v2 {id:199}) 
RETURN sum(e.rate) AS rate_sum, v2.name AS movie GROUP BY movie
'''

Return:

+----------+--------------------------+
| rate_sum | movie                    |
+----------+--------------------------+
| 353.0    | Star Trek: First Contact |
+----------+--------------------------+

stddev_pop()

Returns the population standard deviation of a group of values.

NULL behavior: ignore

Function signature

stddev_pop(num: Numeric) -> DOUBLE | DECIMAL

If num is of data type DECIMAL, the return value is of data type DECIMAL; otherwise, the return value is of data type DOUBLE.

Examples

Example 1:

FOR i IN LIST [1,2,3,4,5,5] RETURN stddev_pop(i) GROUP BY ()

Return:

+--------------------+
| stddev_pop(i)      |
+--------------------+
| 1.4907119849998596 |
+--------------------+

Example 2:

'''
USE movie 
MATCH (v1)-[e:Watch]->(v2 {id:199}) 
RETURN stddev_pop(e.rate) AS rate_stddev_pop, v2.name AS movie GROUP BY movie
'''

Return:

+--------------------+--------------------------+
| rate_stddev_pop    | movie                    |
+--------------------+--------------------------+
| 0.9843295797912273 | Star Trek: First Contact |
+--------------------+--------------------------+

stddev_samp()

Returns the sample standard deviation of a group of values.

NULL behavior: ignore

Function signature

stddev_samp(num: Numeric) -> DOUBLE | DECIMAL

If num is of data type DECIMAL, the return value is of data type DECIMAL; otherwise, the return value is of data type DOUBLE.

Examples

Example 1:

FOR i IN LIST [1,2,3,4,5,5] RETURN stddev_samp(i) GROUP BY ()

Return:

+-------------------+
| stddev_samp(i)    |
+-------------------+
| 1.632993161855452 |
+-------------------+

Example 2:

'''
USE movie 
MATCH (v1)-[e:Watch]->(v2 {id:199}) 
RETURN stddev_samp(e.rate) AS rate_stddev_samp, v2.name AS movie GROUP BY movie
'''

Return:

+--------------------+--------------------------+
| rate_stddev_samp   | movie                    |
+--------------------+--------------------------+
| 0.9897829709943133 | Star Trek: First Contact |
+--------------------+--------------------------+

percentile_cont()

An inverse distribution function that takes a percentile between 0 and 1 as an argument and returns the interpolated value corresponding to that percentile in the sorted group values.

NULL behavior: ignore

Function signature

percentile_cont(num: Numeric, percentile: DOUBLE) -> DOUBLE | DECIMAL
  • If num is of data type INT64, the return value is of data type DOUBLE.
  • If num is of data type DECIMAL, the return value is of data type DECIMAL.
  • If num is of data type DOUBLE, the return value is of data type DOUBLE.

Examples

Example 1:

FOR i IN LIST [1,2,3,4,5,5] RETURN percentile_cont(i, 0.5) GROUP BY ()

Return:

+-------------------------+
| percentile_cont(i, 0.5) |
+-------------------------+
| 3.5                     |
+-------------------------+

Example 2:

'''
USE movie 
MATCH (v1)-[e:Watch]->(v2 {id:199}) 
RETURN percentile_cont(e.rate, 0.5) AS r_percentile_cont, v2.name AS movie GROUP BY movie
'''

Return:

+-------------------+--------------------------+
| r_percentile_cont | movie                    |
+-------------------+--------------------------+
| 4.0               | Star Trek: First Contact |
+-------------------+--------------------------+

percentile_disc()

An inverse distribution function that takes a percentile between 0 and 1 as an argument and returns the actual value closest to that percentile in the sorted group values, rather than an interpolated value.

NULL behavior: ignore

Function signature

percentile_disc(num: Numeric, percentile: DOUBLE) -> DOUBLE | DECIMAL
  • If num is of data type INT64, the return value is of data type DOUBLE.
  • If num is of data type DECIMAL, the return value is of data type DECIMAL.
  • If num is of data type DOUBLE, the return value is of data type DOUBLE.

Examples

Example 1:

FOR i IN LIST [1,2,3,4,5,5] RETURN percentile_disc(i, 0.5) GROUP BY ()

Return:

+-------------------------+
| percentile_disc(i, 0.5) |
+-------------------------+
| 3.0                     |
+-------------------------+

Example 2:

'''
USE movie 
MATCH (v1)-[e:Watch]->(v2 {id:199}) 
RETURN percentile_disc(e.rate, 0.5) AS r_percentile_disc, v2.name AS movie GROUP BY movie
'''

Return:

+-------------------+--------------------------+
| r_percentile_disc | movie                    |
+-------------------+--------------------------+
| 4.0               | Star Trek: First Contact |
+-------------------+--------------------------+

any_value()

Returns an arbitrary value from a group of values.

NULL behavior: ignore

Function signature

any_value(n: E) -> E

E represents any data type.

Examples

Example 1:

FOR i IN LIST [1,2,3,4,5,5] RETURN any_value(i) GROUP BY ()

Return:

+--------------+
| any_value(i) |
+--------------+
| 1            |
+--------------+

Example 2:

The following example is based on the example dataset.

'''
USE movie MATCH (v@Actor) RETURN any_value(v) AS rr GROUP BY ()
'''

Return:

+--------------------------------------------------------------------------------------+
| rr                                                                                   |
+--------------------------------------------------------------------------------------+
| (288399322985267201@Actor:Person{birthDate:1967-08-12,id:85752,name:"Brent Sexton"}) |
+--------------------------------------------------------------------------------------+