Skip to content

Aggregations and Calculations#

Concourse provides built-in aggregation functions for performing calculations across record data. All aggregations are accessed through the calculate() method on the Concourse client.

Available Functions#

count#

Count the number of values stored for a key. Use the special $id$ key to count matching records instead of values.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
// Java
// Count all values for "age" across all records
long total = concourse.calculate().count("age");

// Count values for "age" in specific records
long count = concourse.calculate().count("age",
    Lists.newArrayList(1L, 2L, 3L));

// Count values for "age" in a single record
long count = concourse.calculate().count("age", 1);

Counting Records#

To count the number of records matching a criteria (equivalent to SQL’s SELECT COUNT(*)), use the $id$ intrinsic key:

1
2
3
// Java
long numEngineers = concourse.calculate().count(
    "$id$", "department = Engineering");

1
2
// CaSH
calculate count "$id$" where department = "Engineering"

sum#

Compute the sum of all numeric values stored for a key.

1
2
3
4
5
// Java
Number total = concourse.calculate().sum("salary");

Number deptTotal = concourse.calculate().sum(
    "salary", "department = Engineering");

average#

Compute the arithmetic mean of all numeric values stored for a key.

1
2
3
4
5
// Java
Number avg = concourse.calculate().average("salary");

Number deptAvg = concourse.calculate().average(
    "salary", "department = Engineering");

min#

Find the minimum numeric value stored for a key.

1
2
3
4
5
// Java
Number lowest = concourse.calculate().min("salary");

Number deptMin = concourse.calculate().min(
    "salary", "department = Engineering");

max#

Find the maximum numeric value stored for a key.

1
2
3
4
5
// Java
Number highest = concourse.calculate().max("salary");

Number deptMax = concourse.calculate().max(
    "salary", "department = Engineering");

Filtering with Criteria#

All aggregation functions accept an optional CCL string or Criteria object to filter which records are included in the calculation.

CCL String#

1
2
3
// Java
Number avgAge = concourse.calculate().average(
    "age", "department = Engineering");

Criteria Object#

1
2
3
4
5
6
7
8
// Java
Number avgAge = concourse.calculate().average(
    "age",
    Criteria.where()
        .key("department")
        .operator(Operator.EQUALS)
        .value("Engineering")
        .build());

Filtering by Records#

You can limit calculations to a specific set of records:

1
2
3
// Java
Number total = concourse.calculate().sum(
    "hours", Lists.newArrayList(1L, 2L, 3L));

Or a single record (useful when a key has multiple values):

1
2
3
// Java
Number total = concourse.calculate().sum(
    "scores", 1);

Time-Aware Aggregations#

All aggregation functions support a Timestamp parameter to perform calculations on historical data.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
// Java
// Average salary as of last month
Number historicalAvg = concourse.calculate().average(
    "salary",
    Timestamp.fromString("last month"));

// Sum with criteria at a specific time
Number historicalSum = concourse.calculate().sum(
    "revenue", "active = true",
    Timestamp.fromString("January 1, 2025"));

// Count records at a specific time
long count = concourse.calculate().count(
    "$id$", "department = Engineering",
    Timestamp.fromString("last quarter"));

Functions in Conditions#

In addition to the calculate() API, aggregation functions can be used directly inside CCL conditions as comparison values. This lets you write queries that filter records based on how their values compare to an aggregate.

Call Syntax#

Use a function call on the value side of a condition:

1
2
3
find age > avg(age)
find score >= min(score)
find salary < max(salary, department = "Engineering")

Pipe Syntax#

Use the | operator on the key side:

1
2
find score | avg > 80
find age | sum > 1000

In CaSH#

1
2
3
[default/cash]$ find age > avg(age)
[default/cash]$ find score | avg > 80
[default/cash]$ calculate avg salary where dept = "Engineering"

See the CCL Reference for the full function syntax.

Summary#

Function Description Return Type
count Number of values long
sum Total of numeric values Number
average Arithmetic mean Number
min Minimum value Number
max Maximum value Number

All functions support these parameter combinations:

  • key — across all records
  • key, records — in specific records
  • key, record — in a single record
  • key, ccl — in records matching a CCL query
  • key, criteria — in records matching a Criteria

Each combination also accepts an optional Timestamp for historical calculations.