Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Summarizing, Grouping, and Sorting Query Results

WHERE clause and GROUP BY Next Page

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( UnitPrice ) > 20

But this example is not legal:

WHERE AVG( UnitPrice ) > 20
Using HAVING with aggregate functions

The following 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, you need a query to group the rows in the Products table by name, but eliminate the groups that include only one distinct product:

SELECT Name
   FROM Products
   GROUP BY Name
   HAVING COUNT(*) > 1;
name
Tee Shirt
Baseball Cap
Visor
Sweatshirt

For information about when you can use aggregate functions in HAVING clauses, see Where you can use aggregate functions.

Using HAVING without aggregate functions

The HAVING clause can also be used without aggregates.

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 Products
   GROUP BY Name
   HAVING Name LIKE 'B%';
name
Baseball Cap
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 in the following example.

To list those products available in more than one size or color, for which one version costs more than $10, you need a query to group the rows in the Products 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 Products
   GROUP BY Name
   HAVING COUNT(*) > 1
   AND MAX( UnitPrice ) > 10;
name
Tee Shirt
Sweatshirt