Skip to main content

Aggregate Functions

Aggregates​

  • Avg: average on a subquery
  • Count: count of all objects in subquery
  • Max: maximum on a subquery
  • Min: minimum on a subquery
  • Sum: sum of all objects in subquery

Aggregate function parameters​

  • subquery: String
  • filter: String
  • having: String
  • ordering: [String]
  • limit: Int
  • offset: Int
  • distincton: [String]

Order of Operations​

The process follows a specific order of operations: first, the subquery runs with the filter applied. If distincton is set, duplicate rows are removed from the subquery based on the specified columns. Next, aggregation occurs, and the having filter is applied. Finally, ordering, limit and offset are applied to the results.

subquery: String​

Aggregate queries apply the aggregate function to the first field of the provided GraphQL query in the 'subquery' variable. Any additional fields will be used to group the aggregate, as in SQL's 'group by' clause. If you use a sub-selection for additional fields, be aware that the result's field names will be named like this: [OBJECT_NAME]_[FIELD_NAME]. A simple example of setting a subquery for an avg aggregate that will retrieve the average budget of all the Star Wars movies in our database.

GraphQLJSON Response
{
  Aggregates{
    avg(subquery:"query{films{budget}}")
    
  }
}

{
  "data": {
    "Aggregates": {
      "avg": [
        {
          "avg": 210500000
        }
      ]
    }
  }
}

danger

A subquery must be included with all aggregate function parameters queries.

The rest of the documentation will use JSON variables in place of inline variables.

filter: String​

The filter aggregate function parameter allows you to apply criteria to limit the objects included in an operation by filtering the subquery. By specifying conditions, you can refine the dataset to include only those objects that meet certain requirements, effectively narrowing down large datasets to focus on specific subsets of interest. For example, you can use the filter to find the average budget of films directed by George Lucas.

GraphQLJSON Response
query film_budget(
  $subq: String
  $f: String
){
  Aggregates {
    avg(
      subquery: $subq
      filter: $f
    )
  }
}
variables
{
  "subq": "query{films{budget}}", 
  "f": "director = 'George Lucas'"

}
{
  "data": {
    "Aggregates": {
      "avg": [
        {
          "avg": 88500000
        }
      ]
    }
  }
}

having: String​

The having aggregate function is used to filter the results of an aggregate query. It allows you to apply conditions to the aggregated data, refining the dataset based on the outcomes of the aggregation. This means you can focus on specific subsets of interest that meet certain criteria, effectively narrowing down the aggregated results from the subquery.

GraphQLJSON Response
query film_charaters(
  $subq: String
  $h: String
){
  Aggregates {
    count(
      subquery: $subq
      having: $h
    )
  }
}
variables
{
  "subq": "query {characters{id planet{name}}}",
  "h": "count > 4"
}
{
  "data": {
    "Aggregates": {
      "count": [
        {
          "count": 11,
          "planet_name": "Naboo"
        },
        {
          "count": 13,
          "planet_name": "Unknown"
        },
        {
          "count": 10,
          "planet_name": "Tatooine"
        }
      ]
    }
  }
}

ordering: [String]​

The ordering function parameters either orders the objects in descending (desc) or in ascending (asc) order. The following example will retrieve the count of character id that have that planet as their home planet then display the count in descending order.

GraphQLJSON Response
query film_charaters(
  $subq: String
  $o: [String]
){
  Aggregates {
    count(
      subquery: $subq
      ordering: $o
    )
  }
}
variables
{
  "subq": "query {characters{id planet{name}}}",
  "o": ["count desc"] 
}
{
  "data": {
    "Aggregates": {
      "count": [
        {
          "count": 13,
          "planet_name": "Unknown"
        },
        {
          "count": 11,
          "planet_name": "Naboo"
        },
        {
          "count": 10,
          "planet_name": "Tatooine"
        },
        {
          "count": 4,
          "planet_name": "Corellia"
        },
        {
          "count": 3,
          "planet_name": "Kamino"
        },
        [...],
        {
          "count": 1,
          "planet_name": "Umbara"
        }
      ]
    }
  }
}

limit: Int​

The number of responses retrieved from the database can be restricted with limit. The example below limits the number of responses to two, with no specific order applied.

GraphQLJSON Response
query film_charaters(
  $subq: String
  $o: [String]
){
  Aggregates {
    count(
      subquery: $subq
      limit: $l
    )
  }
}
variables
{
  "subq": "query {characters{id planet{name}}}",
  "l": 2
}
{
  "data": {
    "Aggregates": {
      "count": [
        {
          "count": 1,
          "planet_name": "Concord Dawn"
        },
        {
          "count": 1,
          "planet_name": "Onderon"
        }
      ]
    }
  }
}

offset: Int​

The offset function is used to specify the starting point for retrieving rows and is commonly used in conjunction with limit to implement pagination. The example below counts the number of characters that originate from a specific planet, starting at row 3, and orders the results in descending order.

GraphQLJSON Response
query film_charaters(
  $subq: String
  $p: Int
  $o: [String]
){
  Aggregates {
    count(
      subquery: $subq
      offset: $p
      ordering: $o
    )
  }
}
variables
{
  "subq": "query {characters{id planet{name}}}",
    "p": 2,
  "o": ["count desc"]
}
{
  "data": {
    "Aggregates": {
      "count": [
        {
          "count": 10,
          "planet_name": "Tatooine"
        },
        {
          "count": 4,
          "planet_name": "Corellia"
        },
        {
          "count": 3,
          "planet_name": "Kamino"
        },
        {
          "count": 3,
          "planet_name": "Coruscant"
        },
        [...],
        {
          "count": 1,
          "planet_name": "Umbara"
        }
      ]
    }
  }
}

distincton: [String]​

The distincton function parameter is used to eliminate duplicate values from the result set, ensuring that only unique entries are returned for a specific column. This is particularly useful when you want to retrieve unique values from a column that might have the same value in different rows, thereby reducing redundancy in your query results. The example below is retrieving the average budget for each director in the Star Wars franchise.

GraphQLJSON Response
query average_budget_each_director(
  $subq: String 
  $d: [String]
){
  Aggregates {
    avg(
      subquery: $subq 
      distincton: $d
    )
  }
}
variables
{
  "subq": "query{films{budget director}}",
  "d": ["director"]
}
{
  "data": {
    "Aggregates": {
      "avg": [
        {
          "avg": 85000000,
          "director": "Dave Filoni"
        },
        {
          "avg": 18000000,
          "director": "Irvin Kershner"
        },
        {
          "avg": 317000000,
          "director": "Rian Johnson"
        },
        {
          "avg": 325000000,
          "director": "Richard Marquand"
        },
        {
          "avg": 431000000,
          "director": "J. J. Abrams"
        },
        {
          "avg": 300000000,
          "director": "Ron Howard"
        },
        {
          "avg": 79666666.66666667,
          "director": "George Lucas"
        },
        {
          "avg": 265000000,
          "director": "Gareth Edwards"
        }
      ]
    }
  }
}