Database Reference
In-Depth Information
11.3.2
Selecting DISTINCT or ALL in Group Functions
Most single-expression grouping functions accept the DISTINCT or ALL
(the default) clauses. DISTINCT or ALL is executed on the expression after
the resolution of that expression. All of the functions listed as follows allow
selecting of DISTINCT or ALL values. DISTINCT or ALL clauses are
applied to the expression argument of each function, in the case of an SQL
statement, each row.
AVG ( [ DISTINCT | ALL ] expression ).
COUNT ( { * | [ DISTINCT | ALL ] expression } ).
MAX ( [ DISTINCT | ALL ] expression ).
MIN ( [ DISTINCT | ALL ] expression ).
SUM ( [ DISTINCT | ALL ] expression ).
STDDEV ( [ DISTINCT | ALL ] expression
).
VARIANCE ( [ DISTINCT | ALL ] expression ).
The following query specifies ALL within the COUNT function. The
GROUP BY clause is discussed later in this chapter. See the result in Figure
11.8.
SELECT A.ARTIST_ID, A.NAME "Artist"
, COUNT(ALL ST.ARTIST_ID) "Studio Visits"
FROM ARTIST A, STUDIOTIME ST
WHERE A.ARTIST_ID = ST.ARTIST_ID(+)
GROUP BY A.ARTIST_ID, A.NAME
ORDER BY COUNT(ALL ST.ARTIST_ID) DESC;
Note three points about the query in Figure 11.8:
The ALL default has been explicitly specified in COUNT(ALL
ST.ARTIST_ID).
The COUNT function will always return one row (see Group Func-
tions and Null Values previously in this chapter). The COUNT func-
tion never returns a null value. If the column being counted has null
values, COUNT returns a zero.
