Archie Core provides powerful grouping and aggregation capabilities through its GraphQL API. You can group records by field values, compute aggregate functions (COUNT, SUM, AVG, MIN, MAX, COUNT_DISTINCT), filter groups with HAVING conditions, and sort results by aggregate values.

Query Arguments

When using grouping and aggregations, the following arguments are available on any list query:

Argument	Type	Description
groupBy	[{TableName}GroupBy]	Fields to group results by
aggregateBy	[{TableName}AggregateInput]	Aggregate functions to compute
having	[{TableName}HavingFilterInput]	Filter groups by aggregate results
aggregateSort	[{TableName}AggregateSortInput]	Sort results by aggregate values

These arguments can be combined with standard arguments like filter, first, skip, and orderBy.

GroupBy

The groupBy argument accepts an array of enum values representing the fields to group by. Enum values follow the pattern: the camelCase field name converted to UPPERCASE.

Naming convention:

Column Name (DB)	Field Name (GraphQL)	GroupBy Enum Value
payment_method	paymentMethod	PAYMENTMETHOD
created_at	createdAt	CREATEDAT
status	status	STATUS
category_id	categoryId	CATEGORYID

Example: Group students by active status

Request

query {
  students(groupBy: [ISACTIVE]) {
    items {
      isActive
    }
    count
  }
}

Response

{
  "data": {
    "students": {
      "items": [
        { "isActive": true },
        { "isActive": false }
      ],
      "count": 2
    }
  }
}

AggregateBy

The aggregateBy argument lets you compute aggregate functions over grouped (or ungrouped) data. Each aggregate requires a function, an optional field, and a mandatory alias.

AggregateInput structure

input {TableName}AggregateInput {
  function: AggregateFunction!
  field: {TableName}AggregateField
  alias: String!
}

Available aggregate functions

Function	Description	field required?
COUNT	Count rows	No (uses COUNT(*) when omitted)
SUM	Sum numeric values	Yes
AVG	Calculate average	Yes
MIN	Find minimum value	Yes
MAX	Find maximum value	Yes
COUNT_DISTINCT	Count distinct values	Yes

The field enum follows the same UPPERCASE convention as GroupBy.

The alias is a string you choose to name the result. It appears as a key in the aggregates response object.

Example: Count and average age of students grouped by active status

Request

query {
  students(
    groupBy: [ISACTIVE]
    aggregateBy: [
      { function: COUNT, alias: "totalStudents" }
      { function: AVG, field: AGE, alias: "avgAge" }
    ]
  ) {
    items {
      isActive
    }
    count
    aggregates
  }
}

Response

{
  "data": {
    "students": {
      "items": [
        { "isActive": true },
        { "isActive": false }
      ],
      "count": 2,
      "aggregates": [
        { "totalStudents": 5, "avgAge": 23.4 },
        { "totalStudents": 2, "avgAge": 21.0 }
      ]
    }
  }
}

Each entry in the aggregates array corresponds to the group at the same index in items.

Having

The having argument filters groups based on aggregate results, equivalent to SQL’s HAVING clause. It uses a dedicated input type with three fields.

HavingFilterInput structure

input {TableName}HavingFilterInput {
  alias: String!
  operator: HavingOperator!
  value: Float!
}

• alias: Must match an alias defined in aggregateBy.
• operator: The comparison operator (see table below).
• value: The numeric threshold to compare against.

Available operators

Operator	SQL equivalent
EQUALS	=
NOT_EQUALS	!=
GREATER_THAN	>
GREATER_THAN_OR_EQUAL	>=
LESS_THAN	<
LESS_THAN_OR_EQUAL	<=

Example: Cities with more than 3 students

Request

query {
  students(
    groupBy: [CITYID]
    aggregateBy: [
      { function: COUNT, alias: "studentCount" }
    ]
    having: [
      { alias: "studentCount", operator: GREATER_THAN, value: 3 }
    ]
  ) {
    items {
      cityId
    }
    count
    aggregates
  }
}

Response

{
  "data": {
    "students": {
      "items": [
        { "cityId": "e14638cb-6d72-4a36-b30f-9b763136a7bb" }
      ],
      "count": 1,
      "aggregates": [
        { "studentCount": 5 }
      ]
    }
  }
}

Only groups where the aggregate condition is met are returned.

AggregateSort

The aggregateSort argument sorts the grouped results by an aggregate value, rather than by a regular field.

AggregateSortInput structure

input {TableName}AggregateSortInput {
  alias: String!
  direction: SortDirection!
}

• alias: Must match an alias defined in aggregateBy.
• direction: ASC (ascending) or DESC (descending).

Example: Top 3 cities by student count

Request

query {
  students(
    groupBy: [CITYID]
    aggregateBy: [
      { function: COUNT, alias: "studentCount" }
    ]
    aggregateSort: [
      { alias: "studentCount", direction: DESC }
    ]
    first: 3
  ) {
    items {
      cityId
    }
    count
    aggregates
  }
}

Response

{
  "data": {
    "students": {
      "items": [
        { "cityId": "e14638cb-6d72-4a36-b30f-9b763136a7bb" },
        { "cityId": "0174dc55-d494-4ebc-a0e9-13575461cad4" },
        { "cityId": "a2b3c4d5-e6f7-8901-2345-678901234567" }
      ],
      "count": 3,
      "aggregates": [
        { "studentCount": 5 },
        { "studentCount": 3 },
        { "studentCount": 2 }
      ]
    }
  }
}

Combining All Features

You can combine filter, groupBy, aggregateBy, having, aggregateSort, and first in a single query for complex analytics.

Example: Top 5 active students’ cities with average age above 20, sorted by average age

Request

query {
  students(
    filter: { isActive: { equals: true } }
    groupBy: [CITYID]
    aggregateBy: [
      { function: COUNT, alias: "studentCount" }
      { function: AVG, field: AGE, alias: "avgAge" }
      { function: MAX, field: AGE, alias: "maxAge" }
    ]
    having: [
      { alias: "avgAge", operator: GREATER_THAN, value: 20 }
    ]
    aggregateSort: [
      { alias: "avgAge", direction: DESC }
    ]
    first: 5
  ) {
    items {
      cityId
    }
    count
    aggregates
  }
}

Response

{
  "data": {
    "students": {
      "items": [
        { "cityId": "e14638cb-6d72-4a36-b30f-9b763136a7bb" },
        { "cityId": "0174dc55-d494-4ebc-a0e9-13575461cad4" }
      ],
      "count": 2,
      "aggregates": [
        { "studentCount": 3, "avgAge": 24.3, "maxAge": 28 },
        { "studentCount": 2, "avgAge": 22.5, "maxAge": 25 }
      ]
    }
  }
}

This query:

1. Filters to only active students (filter).
2. Groups by city (groupBy).
3. Computes count, average age, and max age for each group (aggregateBy).
4. Keeps only groups with average age above 20 (having).
5. Sorts by average age descending (aggregateSort).
6. Limits to the top 5 groups (first).

Response Structure

When using aggregations, the response follows the standard Connection type with an additional aggregates field:

Field	Type	Description
items	[{TableName}!]!	The grouped records (one per group, containing the grouped field values)
count	Int!	Number of groups returned
pageInfo	PageInfo!	Pagination info (hasNextPage, hasPreviousPage)
aggregates	JSON	Array of objects, each containing the computed aggregate values keyed by alias

The aggregates array is parallel to items — the aggregate at index i corresponds to the group at index i in items.

Important Notes

• GroupBy and AggregateField enum values use UPPERCASE of the camelCase field name: payment_method → paymentMethod → PAYMENTMETHOD.
• Having aliases must match an alias defined in aggregateBy. If the alias doesn’t match, the having condition is ignored.
• AggregateSort aliases must match an alias defined in aggregateBy.
• COUNT without a field uses COUNT(*), counting all rows in the group.
• Multiple having conditions can be combined — all conditions must be satisfied (AND logic).
• Multiple aggregateSort entries are applied in order of priority (first entry is the primary sort).
• Standard pagination arguments (first, skip, after, before) work with grouped results.
• The filter argument is applied before grouping (equivalent to SQL WHERE), while having is applied after grouping (equivalent to SQL HAVING).