Aggregate

You can Aggregate data in Addons, which can be useful in graphs or other use cases.

If you are interested in learning about Aggregating based on querying the database, you can check out the Query All Records Output documentation.

The following example will show how to do an Addon that returns the data by aggregate. For this example, there will be two tables: a book table containing a book title and genre and a review table containing a table reference to the book table, comments, and a rating. This example will show how to use an Addon to return the average rating of each book.

First, we will create a new Addon in the output tab of the Query all Records function for the books table. We will add the review table to the response.

In this example, we are adding the review database table to the response.

Next, we will choose to return the data as an aggregate.

In this example, will return the data by aggregate.

Step 3 has a few different options for aggregate. The first thing we must do is select how to connect the database tables.

In this example, we are mapping the review table by the table reference book_id to the book table.

Now we have to determine how we wish to Group, Aggregate, and Sort.

First, we will group the data by the book_id. This will organize the data by each book. We can also customize the name.

In this example, we are grouping the data by the book_id of the review database table.

Next, we will choose which field to aggregate the data by and which aggregator to use. There are many options to use for how to aggregate the data:

• Avg - calculates the average number when dealing with data types such as integer or decimal.

• Count - counts the number of values.

• Count distinct - counts the number of distinct values.

• Max - returns the maximum value.

• Min - returns the minimum value.

• Sum - adds the values together and returns a total sum.

• To_list - returns an array or list of all the values.

For this example, we will aggregate the data by the rating and use the aggregator avg to calculate the average rating for each book.

In this example, we are aggregating by the rating field of the review table. Additionally, we are using the aggregator "avg" to return the average of the rating for each book.

Lastly, we can optionally choose how to sort the data or enable paging.

Sorting has two types:

• Ascending - lowest to highest or A to Z

• Descending - highest to lowest or Z to A

In this example, we won't use sorting because we are returning the average so we are just getting one value anyways.

Hit done, optionally customize the name, then confirm how the Addon is mapped, and customize the response if needed.

Now we can see in the output tab how the Addon will extend the response.

In this example, we can see the review_of_book Addon is extending the response with the book_id and rating.

Finally, we can run this API endpoint with the Addon.

In this example, we can see that the Addon returned the average of rating for each book. Educated has an average rating of 4.05 and Just Mercy has an average rating of 4.625.