Filtering groups

You can specify filter conditions for data that is grouped. For example, if order records are grouped by customer, you can select only customers whose order totals exceed $150000. The filter condition for a group is specified using the HAVING clause. This clause behaves like the WHERE clause, but is applicable to groups. The WHERE clause, on the other hand, applies to individual rows.

A SELECT statement can contain both WHERE and HAVING clauses. For example, you can select customers whose order totals exceed $150000, factoring only orders placed after 06/30/2004. The SELECT statement would look like the following:

SELECT CustomerName, SUM(OrderAmount) FROM Orders

WHERE OrderDate > '2004-06-30'

GROUP BY CustomerName

HAVING SUM(OrderAmount) > 150000

How to filter groups

1	Choose Group Conditions.

2	Create a filter condition.

1	In Column, select a column or Build Expression to create an expression.

2	In Operator, select an operator.

3	In Value, select a column or type a constant value.

4	In AND/OR, optionally select AND or OR to specify another filter condition.

Figure 2-9 shows an example of a filter condition specified for groups.

Figure 2-9

A filter condition specified for groups

The following HAVING clause is added to the SELECT statement:

HAVING SUM(CLASSICMODELS.ORDERDETAILS.QUANTITYORDERED * CLASSICMODELS.ORDERDETAILS.PRICEEACH) >= 150000

 

Copyright Actuate Corporation 2012