First Guide to SQL Anywhere Studio
   PART 3. Basic SQL
     CHAPTER 14. Obtaining Aggregate Data       

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.

  To list all sales reps with more than 55 orders:

1. Type the following:

   SELECT sales_rep, count( * )
   FROM  sales_order
   KEY JOIN employee
   GROUP BY sales_rep
   HAVING count( * ) > 55

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

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

HAVING clauses and WHERE clauses can be combined. When combining these clauses, the efficiency of the query can depend on whether criteria are placed in the HAVING clause or in the WHERE clause. Criteria in the HAVING clause restrict the rows of the result only after the groups have been constructed. Criteria in the WHERE clause are evaluated before the groups are constructed, and save time.

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

1. Type the following:

   SELECT sales_rep, count( * )
   FROM sales_order
   KEY JOIN employee
   WHERE sales_rep > 1000 
   GROUP BY sales_rep
   HAVING count( * ) > 55

The following statement produces the same results.

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

1. Type the following:

   SELECT sales_rep, count( * )
   FROM sales_order
   KEY JOIN employee
   GROUP BY sales_rep
   HAVING count( * ) > 55
   AND sales_rep > 1000

The first statement is faster because it can eliminate making up groups for some of the employees. The second statement builds a group for each sales rep and then eliminates the groups with wrong employee numbers. For example, in the first statement, the database engine would not have to make up a group for the sales rep with employee ID 129. In the second command, the database engine would make up a group for employee 129 and eliminate that group with the HAVING clause.

Fortunately, Adaptive Server Anywhere detects this particular problem and changes the second query to be the same as the first. Adaptive Server Anywhere 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.