<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html lang="en-us" xml:lang="en-us">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="security" content="public" />
<meta name="Robots" content="index,follow" />
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
<meta name="DC.Type" content="reference" />
<meta name="DC.Title" content="HAVING clause" />
<meta name="abstract" content="The HAVING clause specifies a search condition for the groups selected by GROUP BY clause." />
<meta name="description" content="The HAVING clause specifies a search condition for the groups selected by GROUP BY clause." />
<meta name="DC.subject" content="SELECT statement, HAVING, example, clause" />
<meta name="keywords" content="SELECT statement, HAVING, example, clause" />
<meta name="DC.Relation" scheme="URI" content="rbafytexas.htm" />
<meta name="DC.Relation" scheme="URI" content="rbafycurs.htm" />
<meta name="DC.Relation" scheme="URI" content="rbafyussisql.htm" />
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1998, 2006" />
<meta name="DC.Format" content="XHTML" />
<meta name="DC.Identifier" content="rbafyhaving" />
<meta name="DC.Language" content="en-us" />
<!-- All rights reserved. Licensed Materials Property of IBM -->
<!-- US Government Users Restricted Rights -->
<!-- Use, duplication or disclosure restricted by -->
<!-- GSA ADP Schedule Contract with IBM Corp. -->
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
<link rel="stylesheet" type="text/css" href="./ic.css" />
<title>HAVING clause</title>
</head>
<body id="rbafyhaving"><a name="rbafyhaving"><!-- --></a>
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
<h1 class="topictitle1">HAVING clause</h1>
<div><p>The HAVING clause specifies a search condition for the groups selected
by GROUP BY clause.</p>
<div class="section"><p>The HAVING clause says that you want <em>only</em> those groups
that satisfy the condition in that clause. Therefore, the search condition
you specify in the HAVING clause must test properties of each group rather
than properties of individual rows in the group.</p>
</div>
<div class="section"><p>The HAVING clause follows the GROUP BY clause and can contain
the same kind of search condition you can specify in a WHERE clause. In addition,
you can specify aggregate functions in a HAVING clause. For example, suppose
you wanted to retrieve the average salary of women in each department. To
do this, use the AVG aggregate function and group the resulting rows by WORKDEPT
and specify a WHERE clause of SEX = <samp class="codeph">'</samp>F<samp class="codeph">'</samp>.</p>
</div>
<div class="section"><p>To specify that you want this data only when all the female employees
in the selected department have an education level equal to or greater than
16 (a college graduate), use the HAVING clause. The HAVING clause tests a
property of the group. In this case, the test is on MIN(EDLEVEL), which is
a group property:</p>
</div>
<div class="section"><pre><strong>SELECT</strong> WORKDEPT, <strong>DECIMAL</strong>(<strong>AVG</strong>(SALARY),5,0) <strong>AS</strong> AVG_WAGES, <strong>MIN</strong>(EDLEVEL) <strong>AS</strong> MIN_EDUC
<strong>FROM</strong> CORPDATA.EMPLOYEE
<strong>WHERE</strong> SEX='F'
<strong>GROUP BY</strong> WORKDEPT
<strong>HAVING MIN</strong>(EDLEVEL)>=16</pre>
<p>Results in:</p>
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" frame="hsides" border="1" rules="all"><thead align="left"><tr><th valign="top" id="d0e91">WORKDEPT</th>
<th valign="top" id="d0e93">AVG_WAGES</th>
<th valign="top" id="d0e95">MIN_EDUC</th>
</tr>
</thead>
<tbody><tr><td valign="top" headers="d0e91 ">A00</td>
<td valign="top" headers="d0e93 ">49625</td>
<td valign="top" headers="d0e95 ">18</td>
</tr>
<tr><td valign="top" headers="d0e91 ">C01</td>
<td valign="top" headers="d0e93 ">29722</td>
<td valign="top" headers="d0e95 ">16</td>
</tr>
<tr><td valign="top" headers="d0e91 ">D11</td>
<td valign="top" headers="d0e93 ">25817</td>
<td valign="top" headers="d0e95 ">17</td>
</tr>
</tbody>
</table>
</div>
</div>
<div class="section"><div class="p">You can use multiple predicates in a HAVING clause by connecting
them with AND and OR, and you can use NOT for any predicate of a search condition.
<div class="note"><span class="notetitle">Note:</span> If you intend to update a column or delete a row, you cannot include
a GROUP BY or HAVING clause in the SELECT statement within a DECLARE CURSOR
statement. These clauses make it a read-only cursor. </div>
</div>
</div>
<div class="section"><p>Predicates with arguments that are not aggregate
functions can be coded in either WHERE or HAVING clauses. It is typically
more efficient to code the selection criteria in the WHERE clause because
it is handled earlier in the query processing. The HAVING selection is performed
in post processing of the result table.</p>
</div>
<div class="section"><p>If the search condition contains predicates involving character,
or UCS-2 or UTF-16 graphic columns, the sort sequence in effect when the query
is run is applied to those predicates. </p>
</div>
</div>
<div>
<div class="familylinks">
<div class="parentlink"><strong>Parent topic:</strong> <a href="rbafytexas.htm" title="Learn a variety of ways of tailoring your query to gather data using the SELECT statement. One way to do this is to use the SELECT statement in a program to retrieve a specific row (for example, the row for an employee). Furthermore, you can use clauses to gather data in a specific way.">Retrieve data using the SELECT statement</a></div>
</div>
<div class="relconcepts"><strong>Related concepts</strong><br />
<div><a href="rbafyussisql.htm" title="A sort sequence defines how characters in a character set relate to each other when they are compared or ordered. Normalization allows you to compare strings that contain combining characters.">Sort sequences and normalization in SQL</a></div>
</div>
<div class="relref"><strong>Related reference</strong><br />
<div><a href="rbafycurs.htm" title="When SQL runs a select statement, the resulting rows comprise the result table. A cursor provides a way to access a result table.">Use a cursor</a></div>
</div>
</div>
</body>
</html>