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 sales_rep, count( * )
FROM  sales_order
GROUP BY sales_rep
HAVING count( * ) > 55

sales_rep	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 sales_rep, count( * )
FROM sales_order
WHERE sales_rep > 1000 
GROUP BY sales_rep
HAVING count( * ) > 55

The 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 sales_rep 
HAVING count( *) > 55
 AND sales_rep > 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.