Use the HAVING function with the GROUP BY function to retrieve aggregate values that meet specific conditions.
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 |
1,142 |
57 |
GROUP BY must always appear before HAVING. In the same manner, WHERE must appear before 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
GROUP BY SalesRepresentative HAVING count( *) > 55 AND SalesRepresentative > 1000instead 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.