User's Guide

Part II. Tutorials

Chapter 9. Obtaining Aggregate Data

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:

• 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:

• 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:

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