Database Reference
In-Depth Information
Figure 11.14
Restricting Groups
with the HAVING
Clause.
as shown in the following query. The result of the next query will be identi-
cal to that of Figure 11.14.
SELECT A.NAME AS ARTIST, A.EMAIL AS EMAIL
, SUM(ST.AMOUNT_CHARGED) - SUM(ST.AMOUNT_PAID) AS OUTSTANDING
FROM STUDIOTIME ST NATURAL JOIN ARTIST A
WHERE (A.NAME BETWEEN 'A%' AND 'P%')
GROUP BY A.NAME, A.EMAIL;
So what is the best time to use the HAVING clause? When you want to
eliminate certain groups of rows based on the results of an aggregate func-
tion. For example, continuing with the previous situation, let's say that
you want to see only the artists who have an outstanding balance greater
than $4,000. Change the query by adding the last line as follows. Execute
the query. Note that in this case as well, the WHERE clause would proba-
bly still be a better choice with respect to performance. Figure 11.15 shows
the result.
SELECT A.NAME AS ARTIST, A.EMAIL AS EMAIL
, SUM(ST.AMOUNT_CHARGED) - SUM(ST.AMOUNT_PAID) AS OUTSTANDING
FROM STUDIOTIME ST NATURAL JOIN ARTIST A
Search WWH ::




Custom Search