SELECT statement

Getting summarizing values

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.

Console

Execute

SELECT model, COUNT(model) AS Qty_model, AVG(price) AS Avg_price
FROM PC
GROUP BY model
HAVING AVG(price) < 800;

SELECT model, COUNT(model) AS Qty_model, AVG(price) AS Avg_price FROM PC GROUP BY model HAVING AVG(price) < 800;

    Scheme

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:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

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:

SELECT [DISTINCT | ALL]{*
| [> [[AS] ]] [,…]}
FROM  [[AS] ] [,…]
[WHERE ]
[[GROUP BY ]
[HAVING ] ]
[ORDER BY ]

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:

Console

Execute

SELECT MIN(price) AS min_price, MAX(price) AS max_price, AVG(price) avg_price
FROM PC;

SELECT MIN(price) AS min_price, MAX(price) AS max_price, AVG(price) avg_price FROM PC;

    Scheme

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.

Console

Execute

SELECT MIN(price) AS min_price, MAX(price) AS max_price, AVG(price) avg_price
FROM PC
HAVING AVG(price) <= 600;

SELECT MIN(price) AS min_price, MAX(price) AS max_price, AVG(price) avg_price FROM PC HAVING AVG(price) <= 600;

    Scheme