HAVING clause |
|||||||||||||||||||
While WHERE clause gives predicate for filtering rows, the HAVING clause is applied after grouping that gives a similar predicate but filtering groups by the values of aggregate functions. This clause is nessesary for checking the values that are obtained by means of an aggregate function not from separate rows of record source in the FROM clause but from the groups of these rows. Therefore, this checking is not applicable to the WHERE clause. Example 5.5.5 Get the count of PC and the average price for each model providing average price is less than $800.
As a result, we get:
Note that the alias (Avg_price) for naming values of the aggregate function in the SELECT clause may not be used in the HAVING clause. This is because the SELECT clause forming the query result set is executed last but before the ORDER BY clause. Below is the execution order of clauses in the SELECT statement:
This order does not correspond to the syntax order of SELECT operator, which is more closer to native language and is generally formed as follows:
Note that HAVING clause can be also used without GROUP BY clause. When GROUP BY clause is omitted, aggregate functions are applying to all target row set of the query, i.e. we shall receive a single row as a result if the target set is not empty. Thus, if the search condition on aggregate values in HAVING clause will be true this row will be deduced, otherwise we shall not receive any row. Let's consider an example. Example 5.5.6 Find out the maximal, minimal, and average prices for PC. The solution to the given task gives the following query:
If we shall add a search condition, say, on the average price: Find out the maximal, minimal, and average prices for PC provided that the average price does not exceed $600.
Suggested exercises: 15, 20, 28, 37, 40, 57, 63, 67, 68, 70, 72, 76, 81, 82, 85, 87, 89, 102, 114, 126, 129 |