Skip to main content

Aggregation

Aggregation performs a calculation on a set of values and returns a single value. Aggregations are powerful functions. Their results provide insight into the gathered values in the database.

You can aggregate any scalar field in at least one way. String fields can only be counted or grouped. But, you can perform aggregations on numerical fields like Integers and Floats. You can perform the following aggregations with Hypi.

  • avg - Calculates the average of a set of values from a field
  • count - Counts the number of records from a field
  • sum – Calculates the sum of a set of values from a field
  • max – Returns maximum from a set of values
  • min – Returns minimum from a set of values

We will use the following schema to work with aggregate functions.

type Book {
    authorid: Int
    title: String
    price: Float
}

type Author {
    name: String
    age: Int
    booklist: [Book!]
}

Examples:​

Let’s add some data in an object to perform aggregations.

mutation Upsert($values: HypiUpsertInputUnion!) {
    upsert(values: $values) {
    id
    }
}

Here author’s age and price of the book are numerical fields. So, we would perform operations on these two fields. Aggregation operations can be performed with or without filters. Filters may have distinct, group-by or where clauses, etc. They are similar to SQL-type database queries.

Aggregate with No Filter​

The below example demonstrates aggregation without any filters. Here, we are simply aggregating values from age and price fields. The function returns the average value, the number of records(count), minimum value, maximum value, and the sum of the values from both fields.

{
  aggregate {
    author {
      age {
        avg
        count
        max
        min
        sum
      }
    }
    book {
      price {
        avg
        count
        max
        min
        sum
      }
    }
  }
}

You may not select a field or an aggregation function, if it's not needed, Hypi will perform calculations only if you select the field or specify the aggregate function. This means you get faster queries by specifying less.

Aggregate Distinct Rows​

You may aggregate unique rows by specifying the filter ‘distinct’ as true. Hypi considers repeated values as just one value. Hence, the count of books has gone down to 3. Only three unique prices have been selected for aggregation. (12.99,5.99,10)

{
  aggregate {
    book {
      price {
        avg(distinct: true)
        count(distinct: true)
        sum(distinct: true)
      }
    }
  }
}

Aggregate Matching Rows​

You may select matching rows ( records with the specific field value) and perform aggregation on those rows. The below example shows the aggregation of prices of books with authorid equal to ‘2’.where clause have been used for row selection. The result returns aggregate values of prices of books of the second author.,i.e. Paulo Coelho

{
  aggregate {
    book(where: "authorid='2'") {
      price {
        avg
        count
        sum
      }
    }
  }
}

Aggregate with groupBy​

You may make specific groups of records and perform aggregate operations on them. Use groupBy clause as a filter. The below example makes two groups by specifying authorid as groupBy field. There are two groups of books with 2 distinct authorids. So, authorid is a key with values 1 and 2. groupValues povide the key (groupBy field) and the value of the key.

‘With’ gets attached to the data type to form the aggregate query function. Use the function bookWith for using the groupby clause. (Here data type is book). You may specify the order to sort the key values,i.e. ascending or descending. (ASC/DESC). The bookWith function returns the aggregate prices of books of two authors.

{
 aggregate {
     bookWith(groupBy: [{ field: authorid, order: DESC }]) {
         price {
             avg
             count
             sum
             groupValues {
                 key
                 value
             }
         }
      }
   }
}

Aggregate by Date​

You may group the fields based upon the dateTime field. Hypi object has the dateTime fields like hypi_created or hypi_updated. The below example has groups based upon the date of creation of objects. dateGranularity can be set to DAYS, HOURS, MINUTES, or SECONDS.

{
  aggregate {
    bookWith(groupBy: [{ field: hypi_created, dateGranularity: MINUTES }]) {
      price {
        avg
        count
        sum
        groupValues {
          key
          value
        }
      }
    }
  }
}

You can know more about dateGranularity in this tutorial.

Read More...