Introduction to Structured Query Language - Database Processing: Fundamentals, Design, and Implementation - page 73

SQL provides one more GROUP BY clause feature that extends its functionality even further.
The SQL HAVING clause restricts the groups that are presented in the result. We can restrict the
previous query to display only groups having more than one row by using the SQL query:

/* *** SQL-Query-CH02-44 *** */
SELECT Department, COUNT(*) AS Dept_SKU_Count

FROM SKU_DATA

WHERE SKU <> 302000

GROUP BY Department

HAVING COUNT (*) > 1

ORDER BY Dept_SKU_Count;

The result of this modified query is:

Comparing this result with the previous one, the row for Climbing (which has a count of 1) has
been eliminated.

Does Not Work with
Microsoft access
aNSI-89 SQL

This query fails in Microsoft Access ANSI-89
SQL for the same reason as the previous query.

Solution: See the solution described in the
previous “Does Not Work with Microsoft Access ANSI-89 SQL” box. The correct
Microsoft Access ANSI-89 SQL for this query is:

/* *** SQL-Query-CH02-44-Access *** */
SELECT Department, Count(*) AS Dept_SKU_Count

FROM SKU_DATA

WHERE SKU<>302000

GROUP BY Department

HAVING Count(*)>1

ORDER BY Count(*);

SQL built-in functions can be used in the HAVING clause. For example, the following is a
valid SQL query:

/* *** SQL-Query-CH02-45 *** */
SELECT COUNT(*) AS SKU_Count, SUM(Price) AS TotalRevenue, SKU
FROM ORDER_ITEM
GROUP BY SKU
HAVING SUM(Price)=100;