How to calculate an average of the set of values using Aggregation

Low-code
, ,
#1

We have covered most of the ArcQL related queries till now. Let’s move on to Aggregation functions.

In this post, we will see how to calculate an average of the set of values from a field.

  • Aggregation can only be performed on numerical fields like Integers and Floats.
  • avg function calculates the average of the set of numbers.
  • It can be performed with or without filters.
  • The distinct filter gives the average of unique rows.
  • where clause selects matching rows with specific field values. avg function then calculates the average of the selected rows.

We will work with the below schema.

type AggObj {
    intFld: Int
    floatFld: Float
}

Let’s insert 7 records in the AggObj.

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 find out the average of all the floatFld values from the 7 records.

{
  aggregate {
    aggObj {
       floatFld {
        avg
      }
    }
  }
}
#result
{
  "data": {
    "aggregate": {
      "aggObj": {
        "floatFld": {
          "avg": 1.2571428571428573
        }
      }
    }
  }
}

Now, we will use the distinct filter to find out the average of unique values. The below query considers repeated values from floatFld as one value. So the count of records becomes 4 instead of 7. The average becomes (1.1+1.2+1.3+1.4)/4 = 1.25

{
  aggregate {
    aggObj {
      floatFld {
        avg(distinct: true)
      }
    }
  }
}

#result
{
  "data": {
    "aggregate": {
      "aggObj": {
        "floatFld": {
          "avg": 1.25
        }
      }
    }
  }
}

Let’s find out the average of the floatFld values where intFld values are 1.
Average : (1.1+1.1) /2 = 1.1

{
  aggregate {
    aggObj (where: "intFld=1") {
      floatFld {
        avg(distinct: true)
      }
    }
  }
}
#result
{
  "data": {
    "aggregate": {
      "aggObj": {
        "floatFld": {
          "avg": 1.1
        }
      }
    }
  }
}

Check the POSTMAN collection for the average 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