Source: https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/fe/functions/aggregate/
Documentation version: v5.3.2

# 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](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/data-admin/show/show-functions/).

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](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/fe/expressions/aggregate/).

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](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/variable-definition/agg/).

## 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](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/data-types/numeric-type/), [string type](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/data-types/c-string-type/) and [temporal type](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/data-types/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](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/fe/predicates/comparison/#comp_op).

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](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/data-types/numeric-type/), [string type](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/data-types/c-string-type/) and [temporal type](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/data-types/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](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/fe/predicates/comparison/#comp_op).

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](https://dev-docs.nebula-graph.io/v5.3.2/database-gql-reference/overview/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"}) |
+--------------------------------------------------------------------------------------+

````