Null Hndling with Group Functions

Group functions ignore NULL values by default. To force Oracle group functions not to ignore NULL values use nvl() function.



For example:

select avg(sal) from emp;

AVG(SAL)
----------------
29333.3333333333

select avg(nvl(sal,0)) from emp;

AVG(NLV(SAL,0))
--------------------
17600

In this example some of the employees where updated with salary of NULL.

Now try another example to understand the GROUP BY clause also ignores the NULL.

select sal, sum(sal) from emp group by sal;

SAL SUM(SAL)
------------------
25000 25000
27000 27000
36000 36000

Four rows selected.

Even though there where two NULL in the sal field, only one row was yielded by GROUP BY clause.