Example : Simple HAVING Clause Examples
The following example shows the use of HAVING as applied to the aggregate results of a single group defined in the SELECT list, which is particularly useful in a SELECT subquery.
SELECT COUNT(employee)
FROM department
WHERE dept_no = 100
HAVING COUNT(employee) > 10;
The following SELECT statements are additional examples of the correct use of the HAVING clause.
SELECT SUM(t.a)
FROM t,u
HAVING SUM(t.a)=SUM(u.a);
SELECT SUM(t.a), SUM(u.a)
FROM t,u
HAVING SUM(t.b)=SUM(u.b);
SELECT SUM(t.a)
FROM t,u
HAVING SUM(t.b)=SUM(u.b)
AND u.b = 1
GROUP BY u.b;
Example : Grouping Departments by Average Salaries
Display salary ranges for specified departments whose salaries average more than $37,000:
SELECT dept_no, MIN(salary), MAX(salary), AVG(salary)
FROM employee
WHERE dept_no IN (100,300,500,600)
GROUP BY dept_no
HAVING AVG(salary) > 37000;
The result is:
dept_no MIN(salary) MAX(salary) AVG(salary)
‑‑‑-‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
300 23,000.00 65,000.00 47,666.67
500 22,000.00 56,000.00 38,285.71
Example : Using a HAVING Clause to Aggregate a Join
The columns named price and sales_qty are from two different tables table_1, unit_price_cost, and table_2, sales_hist. Use the following SELECT statement to find which category of items is sold for a profit margin greater than $1000.
SELECT table_1.category,
(table_2.price - table_2.cost) * SUM (table_1.sales_qty) AS margin
FROM sales_hist AS table_1, unit_price_cost AS table_2
WHERE table_1.prod_no=table_2.prodno
GROUP BY table_1.category, table_2.price, table_2.cost
HAVING margin > 1000;
A subquery can have a join on a view with an aggregate operation and a HAVING clause that references more than one table.
For more information related to the HAVING clause, see: