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.

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

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

As a result, we get:

model	Qty_model	Avg_price
1232	4	425
1260	1	350

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:

FROM
WHERE
GROUP BY
HAVING
SELECT
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]{*
| [<column_expression>> [[AS] <alias>]] [,…]}
FROM <table_name> [[AS] <alias>] [,…]
[WHERE <predicate>]
[[GROUP BY <column_list>]
[HAVING <condition_on_aggregate_values>] ]
[ORDER BY <column_list>]

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:

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

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

with the results being

min_price	max_price	avg_price
350	980	675

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.

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

mssql

🚫

[[ error ]]

[[ column ]]
[[ value ]]

we shall receive empty result set, for 675.00> 600.

Suggested exercises: 15, 20, 28, 37, 40, 57, 63, 67, 68, 70, 72, 76, 81, 82, 85, 87, 89, 102, 114, 126, 129

#HAVING #GROUP BY #The Order of Execution of Clauses in SELECT State

GROUP BY clause Summarizing data using ROLLUP