How to group records and execute aggregation queries

Low-code
, ,
#1

We have seen maximum and minimum values retrieval using Aggregation.

In this post, we will see how to perform aggregation on a group of records

  • Group the records using the groupBy filter
  • Make specific groups of records using groupBy filter and execute aggregation queries.
  • Calculate average, count and sum of the records of each group.
  • With gets attached to the data type to form the groupBy query function.
  • If the data type is AggObj, use the aggObjWith function with the groupBy clause.
  • groupValues provide the key and value of the groups. key is the grouping field with distinct values.
  • Specify ascending/descending order to sort the key values.

We will work with the same schema.

type AggObj {
    intFld: Int
    floatFld: Float
}

Let’s use the same 7 records from the previous post.

mutation {
  upsert(
    values: {
      AggObj: [
        { intFld: 1, floatFld: 1.1 }
        { intFld: 2, floatFld: 1.2 }
        { intFld: 3, floatFld: 1.3 }
        { intFld: 4, floatFld: 1.4 }
        { intFld: 4, floatFld: 1.4 }
        { intFld: 3, floatFld: 1.3 }
        { intFld: 1, floatFld: 1.1 }
      ]
    }
  ) {
    id
  }
}

Sample Query

Let’s group the intFld values and retrieve average, count and sum of the floatFld values within an intFld group.

 {
  aggregate {
    aggObjWith(groupBy: [{ field: intFld, order: DESC }]) {
      floatFld {
        count
        avg
        sum
        groupValues {
          key
          value
        }
      }
    }
  }
}
#result
{
  "data": {
    "aggregate": {
      "aggObjWith": [
        {
          "floatFld": {
            "count": 2,
            "avg": 1.4,
            "sum": 2.8,
            "groupValues": [
              {
                "key": "intFld",
                "value": "4"
              }
            ]
          }
        },
        {
          "floatFld": {
            "count": 2,
            "avg": 1.3,
            "sum": 2.6,
            "groupValues": [
              {
                "key": "intFld",
                "value": "3"
              }
            ]
          }
        },
        {
          "floatFld": {
            "count": 1,
            "avg": 1.2,
            "sum": 1.2,
            "groupValues": [
              {
                "key": "intFld",
                "value": "2"
              }
            ]
          }
        },
        {
          "floatFld": {
            "count": 2,
            "avg": 1.1,
            "sum": 2.2,
            "groupValues": [
              {
                "key": "intFld",
                "value": "1"
              }
            ]
          }
        }
      ]
    }
  }
}

Check the POSTMAN collection for the groupBy query in different programming languages! Click </> and choose the programming language of your choice.

Don’t forget to insert your own Authorization key and Hypi Domain under Headers to test the results!

Run in Postman