Aggregations

COLLECT for grouping, AGGREGATE for computing SUM, AVG, COUNT, MIN, MAX and statistical functions.

Group & Aggregate

Use the COLLECT clause to group documents and perform aggregations like counting, summing, or averaging.

Basic Grouping

Group results by one or more fields to find distinct values.

FOR doc IN users
  COLLECT city = doc.city
  RETURN { city }

Counting Groups

Count the number of items in each group using WITH COUNT INTO.

FOR doc IN users
  COLLECT city = doc.city
  WITH COUNT INTO count
  RETURN { city, count }

Aggregation Functions

Calculate aggregates using functions like SUM, AVG, MIN, MAX.

FOR s IN sales
  COLLECT product = s.product
  AGGREGATE
    total = SUM(s.amount),
    average = AVG(s.amount),
    min_amt = MIN(s.amount),
    max_amt = MAX(s.amount)
  RETURN { product, total, average, min_amt, max_amt }

Collecting Lists

Use COLLECT_LIST to aggregate values from the group into an array.

FOR u IN users
  COLLECT city = u.city
  AGGREGATE userNames = COLLECT_LIST(u.name)
  RETURN {
    city,
    userNames
  }

COLLECT variable = expression [, ...]
[INTO groupVariable]
[WITH COUNT INTO countVariable]
[AGGREGATE variable = FUNC(expression) [, ...]]

Aggregation Functions

Basic Aggregations

SUM(arr)

Sum of all values in the array.

RETURN SUM([1, 2, 3, 4]) --10

AVG(arr)

Average of all values.

RETURN AVG([1, 2, 3, 4]) --2.5

MIN(arr)

Smallest value in the array.

RETURN MIN([5, 1, 9]) --1

MAX(arr)

Largest value in the array.

RETURN MAX([5, 1, 9]) --9

COUNT(arr)

Number of elements in the array.

RETURN COUNT([1, 2, 3]) --3

COUNT_DISTINCT(arr)

Number of unique elements.

RETURN COUNT_DISTINCT([1, 2, 2, 3]) --3

MEDIAN(arr)

Median value of the array.

RETURN MEDIAN([1, 5, 10]) --5

PERCENTILE(arr, p)

Returns the p-th percentile (0-100).

RETURN PERCENTILE([1..100], 95) --95

Statistical Functions

VARIANCE(arr)

Population variance.

RETURN VARIANCE([1, 2, 3, 4, 5]) --2

VARIANCE_SAMPLE(arr)

Sample variance.

RETURN VARIANCE_SAMPLE([1, 2, 3, 4, 5]) --2.5

STDDEV(arr)

Sample standard deviation.

RETURN STDDEV([1, 2, 3, 4, 5]) --1.581...

STDDEV_POPULATION(arr)

Population standard deviation.

RETURN STDDEV_POPULATION([1, 2, 3, 4, 5]) --1.414...

Aggregation Example

FOR u IN users
  LET amounts = (FOR o IN orders FILTER o.user == u._key RETURN o.amount)
  RETURN {
    user: u.name,
    total: SUM(amounts),
    avg: ROUND(AVG(amounts), 2),
    count: COUNT(amounts),
    median: MEDIAN(amounts),
    stddev: ROUND(STDDEV(amounts), 2)
  }

Graph Queries String Functions