Filtering on an aggregate column
If an information object includes a GROUP BY clause, you can restrict the data rows the information object 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 information object 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.
SELECT orderNumber, (SUM(quantityOrdered * priceEach)) AS Total
FROM OrderDetails
GROUP BY orderNumber
Figure 3-42 shows the first 10 data rows returned by the information object.
Figure 3-42  
You can add a HAVING clause to this information object to place a filter condition on the Total column. The following information object returns only rows for which the order total is greater than or equal to 50000:
SELECT orderNumber, (SUM(quantityOrdered * priceEach)) AS Total
FROM OrderDetails
GROUP BY orderNumber
HAVING SUM(quantityOrdered * priceEach) >= 50000
Figure 3-43 shows the first 10 data rows returned by the information object.
Figure 3-43  
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 the Having page instead of the Filters page. Filter conditions that you create using the Filters page are evaluated before filter conditions that you create using the Having page. In other words, filter conditions in the WHERE clause are applied before filter conditions in the HAVING clause.

Additional Links:

Copyright Actuate Corporation 2012