Database Reference
In-Depth Information
Notice that the GROUP BY clause in Figure 11.13 now contains both
the EMAIL and NAME columns from the ARTIST table. The clause
GROUP BY A.EMAIL, A.NAME will force the query to sort primarily by
the e-mail addresses of the artists, not their names as in Figure 11.12.
Note:
The GROUP BY clause column list in Figure 11.13 includes all col-
umns listed in the SELECT statement (ARTIST.NAME and ART-
IST.EMAIL) not affected by any aggregate functions.
11.4.2
Filtering Grouped Results with the Having Clause
The HAVING clause is used as an extension to the GROUP BY clause to
remove selected groups from the result, much like the WHERE clause is
used to filter rows retrieved by the SELECT clause (the WHERE clause fil-
ters rows at the source of data retrieval). The HAVING clause filters the
result of the GROUP BY clause. The GROUP BY clause executes on all
data retrieved after the WHERE clause has filtered the initial selection.
Note:
Filtering using the WHERE clause will nearly always outperform the
HAVING clause. Never replace a WHERE clause with a HAVING clause
as in the query in Figure 11.14.
The result of the following query is shown in Figure 11.14.
COLUMN ARTIST FORMAT A24 HEADING "Artist"
COLUMN EMAIL FORMAT A24 HEADING "Email Address"
COLUMN OUTSTANDING FORMAT $999990.00 -
HEADING "Outstanding"
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
GROUP BY A.NAME, A.EMAIL
HAVING (A.NAME BETWEEN 'A%' AND 'P%');
For best performance, use the WHERE clause to filter out rows before
grouping whenever possible. For example, the query shown in Figure 11.14
could be revised to use the WHERE clause instead of the HAVING clause
