Database Reference
In-Depth Information
Figure 11.9
Multiple Rows
with the Same
ARTIST_ID Are
Counted Once.
Figure 11.9 shows the result. Notice that because the COUNT function
is counting DISTINCT values of the ARTIST_ID, it returns 1 for artists
with one or more visits.
To show how this feature can be useful, add the DECODE function to
the query to list artists as having either worked in the studio or not worked
in the studio, based on the value of the COUNT function. The following
query does this. See the result in Figure 11.10.
COLUMN ID FORMAT 90 HEADING "ID";
COLUMN ARTIST FORMAT A32 HEADING "Artist";
COLUMN VISITS FORMAT A32 HEADING "Atleast 1 Visit ?"
SELECT A.ARTIST_ID AS ID, A.NAME AS ARTIST
, DECODE(COUNT(DISTINCT
(ST.ARTIST_ID)),1,'Yes','No') AS VISITS
FROM ARTIST A, STUDIOTIME ST
WHERE A.ARTIST_ID = ST.ARTIST_ID(+)
GROUP BY A.ARTIST_ID, A.NAME;
