Using Aggregate Functions › Using HAVING

Using HAVING

You can add a search condition to use with an aggregate function.

The HAVING clause allows you to search for a particular condition within each group. HAVING takes the same predicates as WHERE. The clause must specify an aggregate function because it applies to summary rows only. You use a HAVING clause to eliminate groups from the result, just as you use a WHERE clause to eliminate rows.

You can have both a WHERE clause and a HAVING clause in your SELECT statement.

How It's Done

The company is concerned that there are several departments with only a very few employees. To display those departments that have fewer than three employees, enter:

select dept_id, count(emp_id)
       from employee
       group by dept_id
       having count(emp_id) < 3;

The result looks like this:

DEPT_ID       (EXPR)
-------       ------
   1110            2
   3510            2
   3520            1
   3530            2
   5100            2

5 rows processed