Use the nvl function in Oracle Aggregate functions (sum, AVG, etc.) to force null values to be added.

We are introducingOracleIf there is a null value in the given value,OracleThese null values are ignored. This processing method most of the time meets our requirements, but sometimes there may be problems.

For example, when the year ends, the company will reward some employees, but some of them will not be rewarded. For example, if an employee's reward amount is saved in a table, the reward amount corresponding to some employees is null because they are not rewarded at all, now, the boss asks you to calculate the average prize for the entire company.

If you use

Select AVG (bonus) from EMP;

The result is the average reward of those who receive the reward.

What the boss asks is to calculate the average reward amount of all employees in the company.

This is what you should useNvlFunction to forcibly add employees who have not received the rewards.

For example:

Select AVG (nvl (bonus, 0) from EMP;

In ourScottOfSchemaThere is a table inEMP,Although there is no bonus in it, there is anotherColumn Comm .,We can driveCommTo show the prize and test it.

Select AVG (Comm) from EMP;Get

AVG (Comm)

----

550

Select AVG (nvl (Comm, 0) from EMP;Get

AVG (nvl (Comm, 0 ))

------

157.142857

In this way, the statement just now is verified.