Filtering data in database queries provides a way to narrow the set of objects which should be returned by the server. When using the aggregate functions with grouping, there are two filtering steps. The first one is by using the
whereClause condition which identifies the initial set of data objects. If there is a grouping function in a query, the groups are created from that set. It is possible to apply another condition for the grouping function using the
having clause. The diagram below illustrates the process:
The example below retrieves an average box office earnings for the movies which grossed more than $2 billion released after 2012 grouped by the release year. Notice the
having clause which sets the condition for the groups:
having clause can reference an aggregate function by the assigned alias:
The value must be URL-encoded. Notice the spaces are replaced with %20:
Both having and groupBy can use property functions to apply the having and grouping condition on a value calculated by an expression.
The having clause operates only on the group properties. These properties may include information about the related objects as well. For example, the request below counts the number of actors for the movies grouped by the release year and filters out only those where there are more than 3 actors:
Consider the following data table called
All examples presented further reflect the operation logic and behavior as described in the beginning of this topic.
The example below retrieves an average box office value for movies released after 2012 with earnings exceeding $500 million using the
For a detailed description of all input parameters see the Basic Object Retrieval topic of this guide.
After the Codeless Logic runs, the operation returns two objects containing a custom
averageBoxOffice property and the corresponding value representing an average box office earnings. Only two objects were returned since the condition for the
where clause was set to return all box office earnings for movies released after
The next operation is identical to the previous one, but it shows how an alias (
averageBoxOffice) can be used in the request instead of the aggregate function (
You can also include the related information to the query. For demonstration purpose, the
Movie data table has a column
actors which has a relation to another data table. The operation below is set to count the number of actors for each movie and include only those with 3 or more actors.