Database Reference
In-Depth Information
11.3
Special Grouping Function Behavior
Two factors that should be briefly discussed are the way that group func-
tions behave with null values and the use of DISTINCT. Let's begin with
null values.
11.3.1
Group Functions and Null Values
Most functions, except for COUNT(*) and GROUPING, when passed a
null value, will return a NULL result. COUNT(*) will always return one
row. Any other function returns a null value if no rows are found. The NVL
function can be used to replace a NULL with a value as shown in the query
following. The result is shown in Figure 11.7.
SELECT A.NAME, NVL(ST.MINUTES_USED,0) FROM ARTIST A
NATURAL LEFT OUTER JOIN STUDIOTIME ST;
By converting NULLs into zeros, you can then use the rows with zeros
in the group function. For example, calculating the average time per artist
will yield a different result if you use zero for artists without studio time (by
using the NVL function), rather than calculating the average time per artist
where NULL rows are thrown out before the average is done.
Figure 11.7
Group Functions
and Null Values.
