If an information object query includes a GROUP BY clause, you can restrict the data rows the query returns by adding a HAVING clause. The HAVING clause places a filter condition on one or more aggregate columns. An aggregate column is a computed column that uses an aggregate function such as AVG, COUNT, MAX, MIN, or SUM, for example SUM(quantityOrdered * priceEach).
For example, the following query returns order numbers and order totals. The Total column is an aggregate column. The data is grouped by order number and no filter condition is placed on the Total column.
Figure 2-31 shows the first 10 data rows returned by this information object query.
|
Figure 2-31
|
You can add a HAVING clause to this information object query to place a filter condition on the Total column. The following information object query returns only rows for which the order total is greater than or equal to 50000:
Figure 2-32 shows the first 10 data rows returned by this information object query.
The procedures for creating filter conditions for aggregate columns are identical to the procedures for creating filter conditions for other columns, except that you use Having instead of Filters. A query evaluates filter conditions that you create using Filters before it evaluates filter conditions that you create using Having. In other words, the query applies filter conditions in the WHERE clause before it applies filter conditions in the HAVING clause.
