Restricting groups

You have already seen how to restrict rows in a query using the WHERE clause. You can restrict GROUP BY clauses by using the HAVING keyword.

Restricting GROUP BY clauses

To list all sales reps with more than 55 orders, type:

SELECT SalesRepresentative, count( * )
FROM  SalesOrders
GROUP BY SalesRepresentative
HAVING count( * ) > 55

SalesRepresentative	count( * )
129	57
299	114
467	56
1142	57

GROUP BY must always appear before HAVING. In the same manner, WHERE must appear before GROUP BY.

Using WHERE and GROUP BY

To list all sales reps with more than 55 orders and an ID of more than 1000, type:

SELECT SalesRepresentative, count( * )
FROM SalesOrders
WHERE SalesRepresentative > 1000 
GROUP BY SalesRepresentative
HAVING count( * ) > 55

The Sybase IQ query optimizer moves predicates from the HAVING clause to the WHERE clause, when doing so provides a performance gain. For example, if you specify:

GROUP BY SalesRepresentative 
HAVING count( *) > 55
 AND SalesRepresentative > 1000

instead of the WHERE clause in the preceding example, the query optimizer moves the predicate to a WHERE clause.

Sybase IQ performs this optimization with simple conditions (nothing involving OR or IN). For this reason, when constructing queries with both a WHERE clause and a HAVING clause, you should be careful to put as many of the conditions as possible in the WHERE clause.