User's Guide
   PART 1. Working with Databases
     CHAPTER 5. Summarizing, Grouping, and Sorting Query Results       

The HAVING clause: selecting groups of data

The HAVING clause restricts the rows returned by a query. It sets conditions for the GROUP BY clause similar to the way in which WHERE sets conditions for the SELECT clause.

The HAVING clause search conditions are identical to WHERE search conditions except that WHERE search conditions cannot include aggregates, while HAVING search conditions often do. The example below is legal:

HAVING AVG(unit_price) > 20

But this example is not:

WHERE AVG(unit_price) > 20

HAVING clauses can reference any of the items that appear in the select list.

Using HAVING with aggregate functions 

This statement is an example of simple use of the HAVING clause with an aggregate function.

  To list those products available in more than one size or color :

1. You need a query to group the rows in the product table by name, but eliminate the groups that include only one distinct product:

   SELECT name
   FROM product
   GROUP BY name
   HAVING COUNT(*) > 1 

   name
   Baseball Cap
   Sweatshirt
   Tee Shirt
   Visor

Using HAVING without aggregate functions 

The HAVING clause can also be used without aggregates.

  To list all product names that start with letter B:

1. The following query groups the products, and then restricts the result set to only those groups for which the name starts with B.

   SELECT name
   FROM product
   GROUP BY name
   HAVING name LIKE 'B%' 

   name
   Baseball Cap

   Other ways of obtaining do not involve GROUP BY. This formulation was chosen just to illustrate the use of the HAVING clause.

More than one condition in HAVING 

More than one condition can be included in the HAVING clause. They are combined with the AND, OR, or NOT operators, as the following example shows.

  To list those products available in more than one size or color, for which one version costs more than $10:

1. You need a query to group the rows in the product table by name, but eliminate the groups that include only one distinct product, and eliminate those groups for which the maximum unit price is under $10:

   SELECT name
   FROM product
   GROUP BY name
   HAVING COUNT(*) > 1 
   AND MAX(unit_price) > 10

   name
   Sweatshirt
   Tee Shirt

SQL extension 

All of the previous HAVING examples adhere to the SQL/92 standard, which specifies that columns in a HAVING expression must have a single value, and must be in the select list or GROUP BY clause. However, Adaptive Server Anywhere and Adaptive Server Enterprise support extensions to HAVING that allow aggregate functions not in the select list and not in the GROUP BY clause.

The following example uses this extension.

  To list the names of those product names (covering all sizes and colors) for which there are more than 100 in stock:

1. Enter the following query:

   SELECT name
   FROM product
   GROUP BY name
   HAVING SUM(quantity) > 100

   name
   Baseball Cap
   Tee Shirt