Java Reference
In-Depth Information
Since this example groups order data by customer, each row of the result set represents a single
customer so that customer information can be displayed. The aggregate functions act on all the
purchases customers have made, so they, too, can be included in the
SELECT
list:
Note
The fundamental difference between aggregate functions and standard functions is that
aggregate functions use the entire column of data as their input and produce a single
output, whereas standard functions operate on individual data elements .
In addition to using the
GROUP BY
clause to group your results, you may also wish to narrow your set of
groups down to a smaller subset. You can filter grouped data by using the
HAVING
clause, which is
discussed in the
next section
.
Using the HAVING Clause to Filter Groups
There are going to be situations where you'll want to filter the groups themselves in much the same
way as you filter records using the
WHERE
clause. For example, you may want to analyze your sales by
state but ignore states with a limited number of customers.
SQL provides a way of filtering groups in a result set using the
HAVING
clause. The
HAVING
clause
works in much the same way as the
WHERE
clause, except that it applies to groups within a returned
result set, rather than to the entire table or group of tables forming the subject of a
SELECT
statement.
To filter groups, apply a
HAVING
clause after the
GROUP BY
clause. The
HAVING
clause lets you apply
a qualifying condition to groups so that the database management system returns a result only for the
groups that satisfy the condition. Incidentally, you can also apply a
HAVING
clause to the entire result
set by omitting the
GROUP BY
clause. In this case, DBMS treats the entire table as one group, so there
is at most one result row. If the
HAVING
condition is not true for the table as a whole, no rows will be
returned.
HAVING
clauses can contain one or more predicates connected by
ANDs
and
ORs
. Each predicate
compares a property of the group (such as
COUNT(State)
) with either another property of the group
or a constant.
Figure 8-4
shows the use of the HAVING clause to compute a count of customers by state, filtering
results from states that contain only one customer.
