ASA Getting Started
Selecting Aggregate Data
You have already seen how to restrict rows in a result set using the WHERE clause. You restrict the rows in groups using the HAVING clause.
List all sales representatives with more than 55 orders
In Interactive SQL, type the following in the SQL Statements pane and press F5 to execute the statement.
SELECT sales_rep, count( * ) AS orders FROM sales_order KEY JOIN employee GROUP BY sales_rep HAVING count( * ) > 55 ORDER BY orders DESC
| sales_rep | orders |
|---|---|
| 299 | 114 |
| 129 | 57 |
| 1142 | 57 |
| 467 | 56 |
Order of clausesA GROUP BY must always appear before a HAVING clause. If both are present, a WHERE clause must appear before a GROUP BY clause. |
HAVING clauses and WHERE clauses can both be used in a single query. Conditions in the HAVING clause logically restrict the rows of the result only after the groups have been constructed. Criteria in the WHERE clause are logically evaluated before the groups are constructed, and so save time.
For more information, see The HAVING clause: selecting groups of data.
Combining WHERE and HAVING clauses