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 4‑23 shows an example of a filter condition specified for groups.
Figure 4‑23 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
