Skip to main content

Aggregation Functions

Aggregation functions compute summary values across multiple rows, such as totals, counts, or averages.
They are used globally or per group to reduce sets of rows to a single value.

Usage​

Each aggregation defines:

  • $fn: the name of the aggregation (e.g. "avg", "count", "sum")
  • $operands: the expressions to aggregate over

Optional modifiers include:

  • distinct: excludes duplicates before aggregation
  • filter: limits the input values considered
  • orderBy: controls ordering for position-based aggregates like first or last

By default, null values are skipped. If all inputs are null, most aggregations return null (except count, which returns 0). Arguments should be scalar-compatible (e.g. number, text, boolean). Explicit casting may be required if types differ.

$as​

Added in: indicate.dsl.dql@1.0.0
Assigns an alias to an expression. This can rename output columns or label nested computations.

{
  "$as": <string>,
}
Example: 
{
  "$as": "avg_price",
  "$agg": {
    "$fn": "avg",
    "$operands": [
      {
        "$qualifier": { "$table": "orders" },
        "$identifier": "price"
      }
    ]
  }
}

$distinct​

Added in: indicate.dsl.dql@1.0.0
Specifies that only distinct (unique) values should be returned. This is typically used in combination with identifiers or aggregation functions.

{
  "$distinct": <boolean>,
  <expressionKey>: Object ( Expression )
}
Example: 
{
  "$distinct": true,
  "$identifier": "event_name"
}

$sum​

Added in: indicate.dsl.dql@1.0.0
Calculates the sum of values across rows.

{
  "$fn": "sum",
  "$operands": [
    { object (Expression) }
  ]
}
Example: 
{
  "$agg": {
    "$fn": "sum",
    "$operands": [
      {
        "$qualifier": { "$table": "orders" },
        "$identifier": "price"
      }
    ]
  }
}

$avg​

Added in: indicate.dsl.dql@1.0.0
Calculates the average value.

{
  "$fn": "avg",
  "$operands": [
    { object (Expression) }
  ]
}
Example: 
{
  "$agg": {
  "$fn": "avg",
  "$operands": [
    {
      "$qualifier": { "$table": "orders" },
      "$identifier": "price"
    }
  ]
}
  }

$count​

Added in: indicate.dsl.dql@1.0.0
Counts the number of non-null values (or all rows if using a wildcard).

{
  "$fn": "count",
  "$operands": [
    { object (Expression) }
  ]
}
Example: 
{
  "$agg": {
    "$fn": "count",
    "$operands": [
      {
        "$qualifier": { "$table": "orders" },
        "$identifier": "id"
      }
    ]
  }
}

$min​

Added in: indicate.dsl.dql@1.0.0
Returns the smallest value found across all rows.

{
  "$fn": "min",
  "$operands": [
    { object (Expression) }
  ]
}
Example: 
{
  "$agg": {
    "$fn": "min",
    "$operands": [
      {
        "$qualifier": { "$table": "orders" },
        "$identifier": "price"
      }
    ]
  }
}

$max​

Added in: indicate.dsl.dql@1.0.0
Returns the largest value found across all rows.

{
  "$fn": "max",
  "$operands": [
    { object (Expression) }
  ]
}
Example: 
{
  "$agg": {
    "$fn": "max",
    "$operands": [
      {
        "$qualifier": { "$table": "orders" },
        "$identifier": "price"
      }
    ]
  }
}

$first​

Added in: indicate.dsl.dql@1.0.0
Returns the first value in order (requires ORDER BY).

{
  "$fn": "first",
  "$orderBy": [
    { object (OrderBy) }
  ],
  "$operands": [
    { object (Expression) }
  ]
}
Example: 
{
  "$agg": {
    "$fn": "first",
    "$orderBy": [
      {
        "$expression": {
          "$qualifier": { "$table": "orders" },
          "$identifier": "created_at"
        },
        "$direction": "asc"
      }
    ],
    "$operands": [
      {
        "$qualifier": { "$table": "orders" },
        "$identifier": "price"
      }
    ]
  }
}

$last​

Added in: indicate.dsl.dql@1.0.0
Returns the last value in order (requires ORDER BY).

{
  "$fn": "last",
  "$orderBy": [
    { object (OrderBy) }
  ],
  "$operands": [
    { object (Expression) }
  ]
}
Example: 
{
  "$agg": {
    "$fn": "last",
    "$orderBy": [
      {
        "$expression": {
          "$qualifier": { "$table": "orders" },
          "$identifier": "created_at"
        },
        "$direction": "desc"
      }
    ],
    "$operands": [
      {
        "$qualifier": { "$table": "orders" },
        "$identifier": "price"
      }
    ]
  }
}