loading..
Русский    English
21:42

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.

Console
Execute
  1. SELECT model, COUNT(model) AS Qty_model, AVG(price) AS Avg_price
  2. FROM PC
  3. GROUP BY model
  4. HAVING AVG(price) < 800;

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:

  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:

  1. SELECT [DISTINCT | ALL]{*
  2. | [> [[AS] ]] [,…]}
  3. FROM [[AS] ] [,…]
  4. [WHERE ]
  5. [[GROUP BY ]
  6. [HAVING ] ]
  7. [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
  1. SELECT MIN(price) AS min_price, MAX(price) AS max_price, AVG(price) avg_price
  2. FROM PC;
with the results being

min_price max_price avg_price
350.00 980.00 675.00

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
  1. SELECT MIN(price) AS min_price, MAX(price) AS max_price, AVG(price) avg_price
  2. FROM PC
  3. HAVING AVG(price) <= 600;
we shall receive empty result set, for 675.00> 600.

Suggested exercises: 14, 15, 202837, 57, 63, 67, 68, 70, 72, 76, 81, 82, 85, 87, 89, 102, 114, 126, 129

Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100